Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
userid128223
Contributor

Re: Creating A Master Calendar

Thanks Josh. I tried your script it gave me error.

"Generic tables must contain at least 3 fields"

Rob.

I have tried using calander from QV components. However how do you adjust quarter to start at different month using components.

thanks

Employee
Employee

Re: Creating A Master Calendar

Sorry about that.  I failed to name the second column in the inline load.   I have corrected it in my previous post.

-Josh

The bold is what was added:

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

userid128223
Contributor

Re: Creating A Master Calendar

Thanks Josh.

It works. however when i am getting weird dates.

19981203    (YYYYMMDD)   Source

however when i bring that date and try passing thru date function, i get weird numbers.

year(saledate)as year,

I get numbers that is unlike year.

56606

Due to this my master calander function is not working properly.

MVP & Luminary
MVP & Luminary

Re: Creating A Master Calendar

If you want to offset your entire Calendar -- a fiscal calendar -- set the FirstMonth parm to 12 in your call to Qvc.Calendar or Qvc.CalendarFromField. For example:

CALL Qvc.CalendarFromField('OrderDate', 'Fiscal Calendar', 'Fiscal ', '12');

If instead you want a standard calendar, but want to offset just the Quarter field, use the new code extension feature available in Qvc 4.0.

SET Qvc.Global.Extension.Directory=C:\local\QvcExtensions;

and the contents of C:\local\QvcExtensions\CalendarExtFields.qvs would be:

,'Q' & Ceil (month(AddMonths(Date,1)/3) as [$(_fieldPrefix)OffsetQuarter]

If you want to replace the standard Quarter field with the offset one, follow the calendar generation with:

DROP FIELD Quarter;

RENAME FIELD OffsetQuarter TO Quarter;

-Rob

Employee
Employee

Re: Creating A Master Calendar

I believe the issue is your dates are not in a format that QlikView is expecting you can either change the default format or change the date to a format QlikView is expecting.

To change the default format you need to change the statement that sets this.  This is typicaly line 7 on the first tab of the script.

e.g. change  SET DateFormat='M/D/YYYY';  to SET DateFormat='YYYYMMDD';

Alternatively (my preference), change the format of the source data by parsing out the date into its components and putting it back together when you load it.  The expression would look something like this:

MakeDate(Left(SourceDate, 4), Right(Left(SourceDate,2),6), Right(SourceDate,2)) as Date

userid128223
Contributor

Creating A Master Calendar

Can you please explain the below statement

If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,

thanks

userid128223
Contributor

Creating A Master Calendar

Thanks Josh & Rob for your help.

simongoodman
Contributor

Re: Creating A Master Calendar

I have just seen this and I am in the process of creating a fiscal calendar.

How did you get CurYTDFlag and LastYTDFlag to start at the beginning of the fiscal year? I have tried a couple of things unsucessfully.

Similarly how did you get Week to realign with the fiscal months? Or how did you create a fiscal week in the script?

MVP & Luminary
MVP & Luminary

Re: Creating A Master Calendar

Creating fiscal fields is all about using the FirstMonthOfYear parm in the various date functions. For example, see the help for InYearTodate().

-Rob

simongoodman
Contributor

Re: Creating A Master Calendar

Hi Rob

Thanks for that. I understand the concept but failed in execution. I think

I can not see the wood for the trees. 1 is as per Help and 2 is one of my

failed efforts.

1. inyeartodate(date, basedate , shift )

2. inyeartodate(TempDate, $(varToday), 0 )

  • -1 AS CurYTDFlag

Please can you help?

Simon

On Tue, Jun 12, 2012 at 3:17 PM, Rob Wunderlich <