Calendar with flags making set analysis so very simple

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

 

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

Calendar

Calendar

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. 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.

 

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.

 

You can find the application on the QlikView community: http://community.qlik.com/docs/DOC-6593 

 

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

 

Richard

 

Fun With Flags

Fun With Flags

I couldn’t resist using this picture for the post (from Wil Wheaton’s fan site)

 

 

 

 

8 responses to “Calendar with flags making set analysis so very simple

  1. Personally, I like to create my calendar in-line in the table, rather than linking out to a separate table. This makes things slightly more efficient, and I think tidier. It only falls down if there are missing days in the data (frequently this is not the case though). As with anything in software development it’s just a case of considering the options – rather than just having a master calendar as that is the way you have always done it.

    Completely agree about the flags for ease of Set Analysis though!

    Like

  2. Pingback: Loading Multiple QVD files using Where Exists | qlikcentral·

  3. Pingback: Loading Multiple QVD files | qlikcentral·

  4. Pingback: FirstSortedValue | qlikcentral·

  5. Pingback: Where to store your expressions in QlikView? | qlikcentral·

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s