Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal will move to Qlik Community Oct 4. Temporary case outage Oct 3. READ DETAILS
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

View solution in original post

marcus_sommer
MVP & Luminary
MVP & Luminary

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

vsudhakar
Creator III
Creator III

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