First of all, I'm very grateful and amazed by Qlik Sense and the Qlik community. I think I've learned so much more in the last couple of months than in the last couple of years.
Specific thanks to Fabrice Aunez from whom I took the idea for Time Analysis and just adapted it to my requirements.
Many thanks to Henric hic for all his posts and comments. Legend!
Ok, to the point, the intention here was to streamline the creation of a Master Calendar and time dimensions based on one or more than one (if required) important dates on your data model.
This subprocess creates several tables that should facilitate set analysis formulas for Calendar, Financial and even Seasonal analysis such as
- Current Calendar or Financial Period
- Previous Calendar or Financial Period
- Previous Calendar or Financial Year
- Current Calendar or Financial Year to Date
- Previous Calendar or Financial Year to Date
- Current Calendar or Financial Year Moving Total 3 Months
- Previous Calendar or Financial Year Moving Total 3 Months
- Current Season
- Previous Season
- Previous Year Season
And few auto calendar fields for the given date or dates compared against today's date:
- In Year to Date (Flag)
- In Financial Year to Date (Flag)
- In Month to Date (Flag)
- Years Ago (Number)
- Months Ago (Number)
- Month Relative Number (Number)
I created this routine mostly for Australian organisations but then adapted it to allow for other configurations, although not a great deal of testing has been done to be used in other countries, so it'll be up to you to check.
At the moment, the seasonal analysis is done only for Australia, where the Seasons are:
- Summer: Dec / Jan / Feb
- Autumn: Mar / Apr / May
- Winter: Jun / Jul / Aug
- Spring: Sep / Oct / Nov
I imagine that depending on the feedback, I might redo the code to allow for other configurations of Seasons.
I haven't had the chance to work with Qlik View yet, only with Qlik Sense and the main driver for our developments in to empower users to do ad-hoc analytics, which means we need to make it a bit easier for them and with this data model we were able to create all measures that require time analysis as Master Items
I started with Qlik Sense 4 months ago and I only know few functions, so when you look at the code and think 'hmmmm.... there's got to be a better way to do this...', you'll be right!!!! I just haven got there yet...
I hope you find this useful. Many thanks for any constructive feedback!
Kind regards,
Luis
