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.
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
qlikcentral 
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.
LikeLiked 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
LikeLike
Pingback: User Controlled Charts in QlikView | qlikcentral·
Pingback: Mixing Loops within Variables in QlikView - QlikTastic·
Pingback: Automatically Create Point in Time Expressions | qlikcentral·