About 18 months ago I shared a calendar Calendar with flags making set analysis so very simple which was received well.
The basic principle was a calendar with all possible dates linking to the main fact table and flags / pointers describing the type of link:
This gives endless possibilities when creating time comparison calculations. Since then I've developed the idea further with additional functionality added:
- Comparison of Special Dates which can move year on year (For example Easter)
- Two new Fields; From Date and To Date which are associated to dates which fall in-between
- Financial Month, Qtrs, Years added
- Rolling averages and accumulation felds added from HIC's recent blog post: The As-Of Table
I'll be talking through how this calendar works soon on my blog www.qlikcentral.com . I also have developed a technique where expressions and nested expressions are replicated using the flags from the calendar so you don't have to manually set every possibility up!
Look forward to hearing your comments.
Update: V1.5 - Jan 2016, Added %Flag_ThisFiscalYearToDate
Update: Jan 2016, Added QVS file to attachments. QV Licence not required to open