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]

Hi Josh,

This is awesome for the fact data.

How do you suggest we use dates for the forecast calendar?

Thx

G

[Josh_Good]

Hi Gregor,

I'm not sure what is different about your 'forecast calendar'.  This is not reason you can't have multiple date fields (and hence multiple calendars) in the same data model.  If you do that then you will want to name each calendar and the fields in it appropriately.  So you could have a "Forecast Calendar" and "Shipped Calendar" etc.

To do this you would repeat the script for each calendar and adjust the field names as appropriate - this is the easiest way.

A more elegant way would be to use the script below to loop through each calendar you want to create and drive which calendar is created from the inline table (or an external table).  I find this approach to be a bit 'touchy' and often requires a bit of trouble shooting to get it to work just right.

-Josh

Qlik

CalendarNames:

Load * Inline [

CalendarName, Table, CalendarNameSpaces, CalendarNameNoSpaces,

ActualEnd, Data, Actual End, Actual End

EstimatedEnd, Data, Estimated End, Estimated End

Start, Data, Start, Start,

];

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

For i = 0 to (NoOfRows('CalendarNames')-1);

Let varCalendarNameNoSpaces = Peek('CalendarName', $(i), 'CalendarNames');

Let varCalendarNameSpaces = Peek('CalendarNameSpaces', $(i), 'CalendarNames');

Let varCalendarFromTable = Peek('Table', $(i), 'CalendarNames');

Set varDateField = $(varCalendarNameSpaces) Date;

Temp:

Load

min([$(varDateField)]) as minDate,

max([$(varDateField)]) as maxDate

Resident $(varCalendarFromTable);

                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);

                $(varCalendarNameNoSpaces)Calendar:

Load

TempDate AS [$(varCalendarNameSpaces) Date],

week(TempDate) As [$(varCalendarNameSpaces) Week],

Year(TempDate) As [$(varCalendarNameSpaces) Year],

Month(TempDate) As [$(varCalendarNameSpaces) Month],

Day(TempDate) As [$(varCalendarNameSpaces) 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 [$(varCalendarNameSpaces) Month-Year],

ApplyMap('QuartersMap', month(TempDate), Null()) as [$(varCalendarNameSpaces) Quarter],

Week(TempDate) & '-' & Year(TempDate) as [$(varCalendarNameSpaces) Week-Year],

WeekDay(TempDate) as [$(varCalendarNameSpaces) Week-Day]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

NEXT;

Drop Table CalendarNames;

[#4]

Hi Josh.

Is there a way you could provide script for QuarterYear? You have MonthYear and WeekYear? I have tried but to no avail.

Thanks so much.

Barb

[Josh_Good]

Hi Barb,

For QuarterYear this should work:

ApplyMap('QuartersMap', month(TempDate), Null()) & '-' & Year(TempDate) as QuarterYear, 

this will produce a text string so it may not natively sort correctly.  Use Sorty by Expression with the expression

MIn(Date)

-Josh

Qlik

[#6]

It worked beautifully. Thanks again, Josh, for your help. You are so fast and so knowledgeable:) Have a nice weekend.

[#7]

Hi all,

Very interesting topic even  for a newbie. I have tried it and works perfectly. I have a question though. Based on the original post, which expression should I use in set analysis to get YTD and LYTD ? I imagine LastYTDFlag and CurYTDFlag ?

Thank you

[Josh_Good]

Hi Renos,

The the flags in the script result in a 1 or 0 so you can simply multiple the field of interest by that appropriate flag  Something like this:

Sum(Sales * LastYTDFlag)

All records that have not been flagged as LastYTD will become zero.

-Josh

Qlik

[MarcoWedel]

Hi,

another possibility without sorting issues could be:

Dual('Q'&Ceil(Month(TempDate)/3)&'-'&Year(TempDate),QuarterStart(TempDate)) as QuarterYear

without the need for a Quarter mapping table.

hope this helps

regards

Marco

[#10]

I would think the set analysis option of

Sum({<LastYTDFlag={1}>} Sales) would perform better would it not, instead of performing the sum across the fact & calendar tables (plus the whole dataset) like that?

[#11]

Thanks. This is a really interesting piece. I was wondering if it is possible to expand on this so that you could look at an invoice period using the start and end dates. My particular interest would be splitting it by how many days are in a month. i.e.from

To:

I have tried multiple adaptations to no effect.