topic Re: create calander months and years only in QlikView

create calander months and years only

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: create calander months and years only

Gysbert_Wassenaar — Wed, 16 Mar 2016 16:47:24 GMT

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

Re: create calander months and years only

marcus_sommer — Wed, 16 Mar 2016 16:47:34 GMT

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

Re: create calander months and years only

engishfaque — Thu, 17 Mar 2016 05:21:23 GMT

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

Re: create calander months and years only

Anonymous — Thu, 17 Mar 2016 05:27:49 GMT

load

Year(datecolumnname) as Year

Month(Datecolumnname) as month

try the above code

Re: create calander months and years only

rebelfox — Thu, 17 Mar 2016 08:05:01 GMT

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.

Re: create calander months and years only

rebelfox — Thu, 17 Mar 2016 09:27:29 GMT

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.

Re: create calander months and years only

Gysbert_Wassenaar — Thu, 17 Mar 2016 09:29:45 GMT

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