Qlik Community

Qlik Sense Documents

Documents about Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER

Master Calendar Script with Optional Fiscal Year

Employee
Employee

Master Calendar Script with Optional Fiscal Year

Video explanation here: https://www.youtube.com/watch?v=8hDJlc6p5lE

Something that I come across on a regular basis is customers asking about dates in Qlik.  Fiscal calendars, complete calendars, creating current year to date vs last year to date, and matching dates between two date fields are all things that have come up.

The answer to all of these is to create a master calendar.  That said, creating a master calendar is not always the easiest thing to do or learn from online posts.

I have attached 'Master Calendar.zip' that contains two .txt files designed to automatically create a master calendar from your data. 

In 'Master Calendar Variables.txt' you can paste the contents after your current load script in your app and fill in the variables:

- Table name that contains the date

- Whether you want to create a fiscal calendar and which month that fiscal calendar should start in

- Your Date field(s) - if you insert two date fields, it will perform an IntervalMatch

In 'Master Calendar Script.txt' you can either paste the contents below the Variables or use an Include statement  below the Variables to call on the script.

That's it!  Hope this helps!

Attachments
Comments
Partner
Partner

Brilliant! Thank you!

0 Likes
Contributor
Contributor

This is great! In the script you have InAnyYTD, but how would you write InAnyQTD and InAnyMTD? 

I.e. FY21 QTD vs. FY20QTD vs. FY19 QTD 

0 Likes
Version history
Revision #:
4 of 4
Last update:
‎2020-05-06 05:10 PM
Updated by:
Employee