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
Highlighted
Employee
Employee

Creating A Master Calendar

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;

70 Replies
Not applicable

Creating A Master Calendar

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

Not applicable

Re: Creating A Master Calendar

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 <=

Employee
Employee

Re: Creating A Master Calendar

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

2012-05-24_0014.png

Not applicable

Re: Creating A Master Calendar

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

Employee
Employee

Re: Creating A Master Calendar

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

userid128223
Contributor

Creating A Master Calendar

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

MVP & Luminary
MVP & Luminary

Creating A Master Calendar

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

Employee
Employee

Re: Creating A Master Calendar

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;

MVP & Luminary
MVP & Luminary

Re: Creating A Master Calendar

How about:

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

-Rob

http://robwunderlich.com