Creating A Master Calendar

[Josh_Good]

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

http://youtu.be/ScdIQvWzVFs

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;

[#2]

I am working with a customer where their Q1 is Oct/Nov/Dec… the really nice master calendar above returns Q1 = Jan/Feb/March... what would I have to do to have Q1 = Oct/Nov/Dec and then Q2 = Jan/Feb/Mar etc etc.. I am using 11.2 btw.

I messed around with the scrip  a bunch to see if I could make it happen... no luck so far and am crunched for time.

Tx!

k

[Josh_Good]

You can use the AddMonths function to move the calendar date forward or backwards to be the correct fiscal date.  In your case you will want to add three months to the date so Oct 1st moves to the first day of the year (Jan 1st) from there you can apply the vaious funcitons to make your calendar.  Note you can keep both fiscal and calendar dates in the same table.

So for FiscalQuarter it would look like this....

MasterCalendar:

Load

              ...

               ApplyMap('QuartersMap', month(AddMonths(TempDate,3)), Null()) as FiscalQuarter,

              ...

Resident TempCalendar

[#4]

Hey Josh,

I'm a newbie. The original script you shared is not working for me. It gives the following errors:

1) Table Not Found Temp:

2) Field not found - <<=>

   TempCalendar:

3)Error in expression: MonthStart takes 1-2 parameters

   MasterCalendar:

Can you please guide me about this?

[#5]

Hi Josh,

Love the calendar and have been using it with no issues for a while now.

But,

I have a new app where I need to show sum of a measure from last week.

Using Max(Week) is giving me "52" (obviously from 2013), but what I need to see is week 3 from 2014.

Any ideas for me ?

I'm trying to show the most active customer last week basically.

Thanks

T

[Josh_Good]

Tony,

I would setup a flag in the calendar that flags all dates that are 'last week'.  First define what today is with a variable and then check and see the last week dates.  so something like this:

Let vToday = Today(0);

///A Bunch of Script

///The Beginning of the Calendar Script...

MasterCalendar: 

Load 

               TempDate AS OrderDate, 

               //....

              if (WeekStart(TempDate) = WeekStart($(vToday)-7), 1) as LastWeekFlag

               //...

Resident TempCalendar

//The Rest of the Calendar Script

Hope that helps,

Josh

PS I like your Mr. T avatar. "I pity the fool who doesn't use QlikView!"

[#7]

Thank you so much Josh,

I am not sure if I am doing the pitying or being the fool lately!

[marko_rakar]

Just a quick note;

I have spent good deal of time to understand why my master calendar is not working and the error I got was the error you mentioned above.

The script is absolutely ok, it is just that the because of complexity of my data, I am using "QUALIFY" statement previously in the script and in order to make master calendar work you either have to adress that and correct references to exact and full field names or, just in front of your master calendar script add "UNQUALIFY *;"

I hope this helps, I spent too much time figuring this out and I am surprised that nobody else mentioned this rookie mistake.

[#9]

Hi Josh,

Am new with Qlikview and have little problem, hope i find help here.

So, i created a MasterCalendar liked showed in the tutorial. (Thx by the way for this)

Then i created two variables FromDate and ToDate but when i click on the Calendar I only see 1 Month instead of all the dates shows in the preview of the MasterCalendar. I would like to show all months and days from my Calender, how can i do this?

The two variables have as default value 'Varmaxdate' from the Mastercalendar script.

Thanks in advance for your help.

MJ

[Josh_Good]

Hi MJ,

Great to hear you found this video helpful.  Have you seen my posting on selecting arbitrary date ranges?

Selecting Arbitrary Date Ranges

I think it is exactly what you are looking for 

-Josh

[#11]

Hi Josh,

thanks a lot, that one was perfectly what i needed

Again Thanks and wish u a very nice afternoon from Hamburg

Bye