create calander months and years only

[rebelfox]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[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]

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]

load

Year(datecolumnname) as Year

Month(Datecolumnname) as month

try the above code

[rebelfox]

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]

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