Qlik Community

QlikView Documents

Documents for QlikView related information.

Master Calendar Data

Arjunarao
Honored Contributor II

Master Calendar Data

Hi All,

Below script helps in generating the Master calendar. 

Hope this will help you.

LET vDateMin=num(makedate(2014,01,01));

LET vDateMax=floor(monthend(today()));

LET vDateToday = num(today());

Calender:

LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY') AS Date,

month(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Month,

year(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Year,

  monthname(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Monthname,

  Week(Date($(vDateMin) + RowNo() - 1,'DD-MMM-YYYY')) AS Week

  

AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

Capture.PNG

Attachments
Comments
Not applicable

what is the use of master calendar?

Not applicable

Have run the script it works perfect. Please can I input QUARTER into the script?

oknotsen
Honored Contributor III

OLAWUMI oladeji wrote:

Please can I input QUARTER into the script?

Just search for "Master Calendar". There are many versions of this script.

If you follow either a Developer training for QlikView or a Data Modeling training for Qlik Sense, the instructor will even give you a much more complete version of this script, one that includes Quarter.

vireshkolagimat
Contributor II

Hi ,

why you have used 1 after autogenerate fucntion? and also why Iterno()-1 why not Iterno()+1 ?

please help me understand this.

thanks

Viresh

Not applicable

Hi Viresh,

Autogenerate 1 means data will load once starting from Minimum Date to Maximum Date. If you use Autogenerate 2 then data will load twice and for 3 it will load thrice and so on.

IterNo() returns an integer and the first iteration is number 1. Here, from the above script Variable Minimum Date (vDateMin) is 01/01/2014 and its numeric value is 41640. By adding IterNo() to vDateMin you are adding +1 to the actual minimum date. So, at the end you have to Deduct with same value. Hence it is IterNo()-1

$(vDateMin) + IterNo() - 1  means, 41640+1-1 and Returns 41640 i.e., 01/01/2014 (Minimum Date)

Instead IterNo()-1, if you do IterNo()+1 then Minimum Date will be 41642 and will return 01/03/2014 (Jan 3, 2014)

Wish I am clear.

Thank you

Version history
Revision #:
1 of 1
Last update:
‎09-13-2015 09:40 PM
Updated by: