Date Comparison with a Powerful Calendar

About 18 Months ago I shared a calendar which allows you to calculate values over many different periods. Yesterday, Last Month, Last Month Comparative, 52 Weeks ago, etc.

This proves really useful especially when developing dashboards that either need a lot of these types of expressions or when you wish to discount certain days, weekends for example. It also works a lot faster than normal set analysis when working with large data volumes.

This year a customer requested the ability to compare special dates such as Easter. These dates are partially interesting as the dates which they fall in changes year by year.

In this new revision the Calendar can not only handle these special dates but also has further functionality:

  • From and To Dates
  • Financial Months, Quarters and Year
  • Cumulative and rolling calculations

How it works:

The calendar 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:

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)

The QVW can be found here: https://community.qlik.com/docs/DOC-13536

https://www.youtube.com/watch?v=ckb57Mjrheg

 

https://www.youtube.com/watch?v=fRidZbWd5hk

Thanks

Richard

5 responses to “Date Comparison with a Powerful Calendar

  1. This is fantastic! And if you tell me this can be used with 4-4-5 fiscal accounting months, with fiscal year running from Sep-Aug, I’ll personally recommend you for superhero status.

    Liked by 1 person

    • Thanks Brian 🙂

      You can use the 4-4-5 fiscal accounting months although you currently have to specify the periods (In Special Dates). I’m looking to make the Financial aspect of the calendar more dynamic in future releases. The power really lies in comparing periods which this calendar does really well.

      I want to keep this as open source as possible so any ideas you have please let me know or even update the code.

      Cheers

      Richard

      Like

  2. Pingback: User Controlled Charts in QlikView | qlikcentral·

  3. Pingback: Mixing Loops within Variables in QlikView - QlikTastic·

  4. Pingback: Automatically Create Point in Time Expressions | 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