topic Creating A Master Calendar in QlikView

Creating A Master Calendar

Josh_Good — Wed, 07 Mar 2012 02:37:46 GMT

This videos show how to create a Master Date Calendar in QlikView. The script mentioned in the video is below.

QuartersMap:
MAPPING LOAD 
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
               min(OrderDate) as minDate,
               max(OrderDate) as maxDate
Resident Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
               $(varMinDate) + Iterno()-1 As Num,
               Date($(varMinDate) + IterNo() - 1) as TempDate
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
               TempDate AS OrderDate,
               week(TempDate) As Week,
               Year(TempDate) As Year,
               Month(TempDate) As Month,
               Day(TempDate) As Day,
               YeartoDate(TempDate)*-1 as CurYTDFlag,
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
               WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Creating A Master Calendar

Wed, 23 May 2012 17:00:45 GMT

Thanks! For anyone looking to learn the fundamentals of using a master calendar, I would definitely recommend watching this video.

Re: Creating A Master Calendar

Wed, 23 May 2012 17:31:03 GMT

Josh, I've tried to implement the Master Calendar after having watched your video, and I feel like I understand how everything works, but I'm getting an error on reload with the temp table/autogenerate. Any idea why that would be?

Here is my error:

Field not found - <<=>

TempCalendar:

LOAD

+ Iterno()-1 As Num,

Date( + IterNo() - 1) as TempDate

AutoGenerate 1 While + IterNo() -1 <=

Re: Creating A Master Calendar

Josh_Good — Thu, 24 May 2012 07:18:03 GMT

It looks like you are not generating values for varMinDate (and like also for varMaxDate) for some reason. Try using the degubber and running the script using 'Step' to confirm this (see image which show what you should be seeing if you are generating values). The reason for this could either be your Temp table is empty or there is some sort of syntax error on the line that sets the variable varMinDate (line 37 of the image below).

Re: Creating A Master Calendar

Thu, 24 May 2012 15:33:39 GMT

That's what I thought too Josh, so here is what I did.

To try and debug, I removed everything but the below:

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(PerformanceDate) as minDate,
max(PerformanceDate) as maxDate
Resident DimPerformanceDate;

When I run the above, and display mindate and maxdate in a listbox it definitely contains values.

But once it gets to that autogenerate line it can't find them...

Also, another error I see in debugger is that monthstart must contain two values. (Not sure)

Does your script only work on a certain version? Because these problems are really strange.

I'm running QV9. Figured I'd throw that out there before I keep trying to troubleshoot something that may not work.

Thanks for your response,

-Anthony

Re: Creating A Master Calendar

Josh_Good — Fri, 25 May 2012 23:30:26 GMT

Hi Anthony,

Yes the script should work in V9. I origionally started using in V9 myself. I'm a bit at a lost as to why you are running into an error. The only thing I can think of is that your data is not being recongized as dates by QlikView. Maybe see if you can get the script to work with different data (to rule that out).

Regards,

Josh

Creating A Master Calendar

userid128223 — Fri, 01 Jun 2012 16:27:24 GMT

Hi Josh

Your master calander calculates quarter

q1 = jan to march

q2= apr to jun

what if the quarter starts from dec.

q1=dec - feb

q2= mar - may

how do you make changes to master calander to accomodate that.

please help

thanks

Creating A Master Calendar

rwunderlich — Fri, 01 Jun 2012 16:52:02 GMT

We are all coding MasterCalendars over and over again. It's an interesting excercise to learn scripting, but if you just want to get the calendar done, consider using Qlikview Components http://qlikviewcomponents.org, the free open source QV script library.

http://qlikviewnotes.blogspot.com/2012/01/easy-period-analysis-using-qlikview.html

-Rob

Re: Creating A Master Calendar

Josh_Good — Fri, 01 Jun 2012 16:54:09 GMT

Hi,

To do this you will need to change the Mapping table to match your quarters. I would either drive it off fiscal year definitions from a data source or using an inline load. The inline load would look like this:

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

You may also want to define a Fiscal Year as well. In the load statement that defined the Master Calendar table you could add a line something like this:

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

Putting it all together your script would look something like below.

I hope that helps!

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q2

4, Q2

5, Q2

6, Q3

7, Q3

8, Q3

9, Q4

10, Q4

11, Q4

12, Q1

];

Temp:

Load

min(OrderDate) as minDate,

max(OrderDate) as maxDate

Resident Orders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

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

Week(TempDate) & '-' & Year(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Re: Creating A Master Calendar

rwunderlich — Fri, 01 Jun 2012 17:02:31 GMT

How about:

'Q' & Ceil (month(AddMonths(Tempdate,1)/3) as Quarter

-Rob

http://robwunderlich.com

Re: Creating A Master Calendar

userid128223 — Fri, 01 Jun 2012 18:52:15 GMT

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

Re: Creating A Master Calendar

Josh_Good — Fri, 01 Jun 2012 19:38:11 GMT

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

Re: Creating A Master Calendar

userid128223 — Fri, 01 Jun 2012 19:59:41 GMT

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.

Re: Creating A Master Calendar

rwunderlich — Fri, 01 Jun 2012 20:25:47 GMT

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

Re: Creating A Master Calendar

Josh_Good — Tue, 05 Jun 2012 00:08:54 GMT

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

Creating A Master Calendar

userid128223 — Tue, 05 Jun 2012 17:08:24 GMT

Can you please explain the below statement

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

thanks

Creating A Master Calendar

userid128223 — Tue, 05 Jun 2012 17:12:43 GMT

Thanks Josh & Rob for your help.

Re: Creating A Master Calendar

simongoodman — Tue, 12 Jun 2012 09:12:57 GMT

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?

Re: Creating A Master Calendar

rwunderlich — Tue, 12 Jun 2012 14:17:16 GMT

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

-Rob

Re: Creating A Master Calendar

simongoodman — Tue, 12 Jun 2012 16:01:10 GMT

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 <