Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rebelfox
Creator
Creator

create calander months and years only

I have found many examples of how to create a calendar table in a QlikView report to act as a selection dimension.

Has anybody got an example of how to create a calendar of just months and years?

I want to match to the calendar on a field that has a YYYYMM value so having a calendar dimension for every day is a bit wasteful.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

The principle is the same. Only instead of adding 1 for each day with IterNo() you use MonthStart( DateField, IterNo() -1) to get the next month.

LET vMin = Num(MakeDate(2010));

LET vMax = Num(MakeDate(2017));

Calendar:

LOAD Month, Year(Month) as Year;

LOAD

     Date(MonthStart($(vMin),IterNo()-1),'YYYYMM') as Month

AutoGenerate 1

While MonthStart($(vMin),IterNo()-1) <= $(vMax);


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

The principle is the same. Only instead of adding 1 for each day with IterNo() you use MonthStart( DateField, IterNo() -1) to get the next month.

LET vMin = Num(MakeDate(2010));

LET vMax = Num(MakeDate(2017));

Calendar:

LOAD Month, Year(Month) as Year;

LOAD

     Date(MonthStart($(vMin),IterNo()-1),'YYYYMM') as Month

AutoGenerate 1

While MonthStart($(vMin),IterNo()-1) <= $(vMax);


talk is cheap, supply exceeds demand
marcus_sommer

I would use a "normal" master-calendar which could be used by many applications which may have a different need of granularity and I think that the overhead from a daily level is very small compared to the applications itself so that you wouldn't notice any difference in performance. More informations could you find here: How to use - Master-Calendar and Date-Values.

- Marcus

engishfaque
Specialist III
Specialist III

Dear Roy,

Use listed below script,

LET vMinDate = Num(Date('01/01/2015','DD/MM/YYYY')); //Example. Start Date is September 2015
LET vMaxDate = Num(Date(Today(),'DD/MM/YYYY')); //Today Date is 17/03/2016

LOAD DISTINCT MonthStart($(vMinDate) + IterNo() - 1)   as Temp_Date

AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Kind regards,

Ishfaque Ahmed

Anonymous
Not applicable

load

Year(datecolumnname) as Year

Month(Datecolumnname) as month

try the above code

rebelfox
Creator
Creator
Author

That's a good idea.   So you would create a master calendar in a QVD once and then load it on all reports with the required elements.  I think I'll do that.

rebelfox
Creator
Creator
Author

The code certainly works but it is more sophisticated than I have encountered before.

Can you explain why there are two LOAD statements and why they are not rejected?  I have never seen a table created with two LOAD's before.

Gysbert_Wassenaar

That's called a preceding load. The results of the lower load are passed upwards in the preceding load. See this blog post for more information: Preceding Load


talk is cheap, supply exceeds demand