Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY

Calendar with flags making set analysis so very simple

richard_pearce6
Luminary Alumni
Luminary Alumni

Calendar with flags making set analysis so very simple

NOTE: A more recent version with added functionality can be found here: Calendar with AsOf Flags, Compare Easter to Easter

Hello

Calendars are one of the first things developed when working with Qlik and most follow a similar format.

Often called “Master Calendar” the name misleads as best practice design means multiple calendars are often
required. http://community.qlik.com/docs/DOC-6502

Once you have a master calendar you’ll often find yourself creating complex set analysis to calculate date ranges or comparative calculations like Month to Date or Previous Month. You can find some good examples here:

http://community.qlik.com/docs/DOC-6163

Set Analysis for Rolling Periods

Set Analysis for certain Point in Time

This QVW describes an alternative way. The calendar here creates all possible combinations of dates and flags their relationship with each other.

Calendar1.png

Here we can see it in action. The Date Selected combined with the Flag returns the Possible Key Dates which are linked to the fact table. The Set Analysis becomes very intuitive and easy to maintain:

Yesterday:=sum({<Date={'$(=max(Date))'},%Flag_PreviousDay_M01={1}>}Sales)

Additional flags shown here you take the concept further.

MTD:=sum({<Date={'$(=max(Date))'},%Flag_ThisMonthToDate={1}>} Sales)
Previous MTD:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonthComparative_M01={1}>} Sales)
Previous Month:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M01={1}>}Sales)
Same Month Last Year:=sum({<Date={'$(=max(Date))'},%Flag_PreviousMonth_M12={1} >}     Sales)


This idea was originally shown to me by a colleague Jason Michaelides who was inspired by A New Choice of Armor: The Flag Matrix | iQlik - Everything QlikView. This version takes the concept and by simplifying the code means its easier to understand and allows you to develop further to meet your individual requirements.

The basic development principles:

Firstly a standard Calendar is created using AutoGenerate. I also take the opportunity to incorporate Business Day flags (set by loading regional public holidays).

A Cartesian Product is then produced by left Joining Distinct Dates with itself without the key. This gives us all the possible date
combinations.

I then create the flags; Firstly Point In Time Flags such as Today, This Week, This Month, etc. Then period flags such as Previous Day, Previous Month, Previous Month to Date, etc.

Update - Following the comments I've added more code to the CLEAN UP tab to remove any lines where no flags have been set and any lines where the Possible Date is higher than the selected date which reduces the tables size by over 50%

Update - Fixed a bug resulting from the previous update

Update 22-Aug-2014 - Fixed a bug which reduces the number of rows in the final calendar

I hope this of some use to people, I would love to hear your comments and further suggestions.

Richard

qlikcentral | Understand / Create / Inform

Attachments
Comments
alex_millan
Creator III
Creator III

Thanks for sharing

0 Likes
abhinav301
Contributor II
Contributor II

Thanks for sharing ur creative knowledge

0 Likes
beck_bakytbek
Master
Master

Hi Richard,

do you have this issue in qlik-sense file? Becaue i am using the personal edition of QlikView 11 and i am not able to open your file.

Thank a lot in advance

Beck

0 Likes
nihhalmca
Specialist II
Specialist II

Thanks for sharing, very helpful.

0 Likes
Quy_Nguyen
Specialist
Specialist

What a nice idea! Thank you so much for sharing.

0 Likes
beck_bakytbek
Master
Master

Hi Richard,

do you have this issue in Qlik Sense-File format?

Thanks a lot in advance

Beck

0 Likes
Quy_Nguyen
Specialist
Specialist

Hi Beck,

You can convert qvw to qvf using convert feature in: (your host)/dev-hub/converter

0 Likes
beck_bakytbek
Master
Master

Hi Quy,

do you have this file in Qlik Sense Format-File?

0 Likes
Quy_Nguyen
Specialist
Specialist

Yes of course. But i can not attach it here. May i have your email?

0 Likes
ID63965
Contributor
Contributor

Hello 

I am also using QlikSense and have no example Sheet  in the App.

It is possible to post the example for QlikSense? I can not convert it.

Thx.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-05-15 06:00 AM
Updated by: