Analytical Need
A common requirement in data analysis is to be able to view and compare slices of data from different time periods to current date. Business applications for this vary, from identifying trends to predicting future behavior, requirements or issues.
Modeling Challenge
How do we mark every period with all of those that come before it?
A proper data modeling will help you answer this kinds of questions with:
YTD – From beginning of the Year until day of Today
QTD – From beginning of the Quarter until day of Today
MTD – From beginning of the Month until day of Today
For example marking YTD: if today is June 26th, we'd want to flag all the dates of every year (not just this year) from January 1st until June 26th. The same for Quarter & Month.
With Sisense you can easily perform these calculations and we have a few resources to help you get started.
- Calculate MTD, QTD, and YTD Values - (you are here)
- Custom Month and Year to Date to Last Year MTD and QTD Calculations
- Fiscal Year Adjustments
When the quick functions in the dashboard do not answer this need, we will need to model it in the cube.
Solution
Logic
Define new flags in the Dim date table (either as a new field or in the custom query)
YTD - there are 2 possible cases:
- if the date is less than today's month then it is YTD,
- if the date in today's month and the day of the month is smaller or equal to today then it is YTD.
- Otherwise it is not.
For Example: today is August 23. Anything in January to July is YTD and also anything from August 1st to August 23 is YTD. Of any Year.
MTD – if The date in today's month and the day of the month is smaller or equal to today then it is MTD. Otherwise it is not.
QTD – If the date is on the same serial month* and the day of the date is smaller or equal to the day of today, then it is QTD. If the date is in a lower serial month than today and the date is larger than the first day of the quarter, then it is QTD. Otherwise it is not.
* Definition: Serial month is 1 for April, 2 for May, 3 for June, 1 for July etc.
Execution (SQL)
Dim date table is aliased as d
YTD:
CASE WHEN getmonth(d.Date) < getmonth(now()) THEN 1
WHEN getmonth(d.Date) = getmonth(now()) AND getday(d.Date) <= getday(now()) THEN 1
ELSE 0 END AS isYTDMTD:
CASE WHEN getday(d.Date) <= getday(now()) THEN 1 ELSE 0 END AS isMTDDashboard Result
In order to use the calculation of YTD / QTD / MTD you will need to add 2 filter for each measure you use:
1) Value of isYTD / isQTD / isMTD flag as 1
2) Time frame filter (This Year, Last Year, This Quarter, 4 Quarters ago etc.)
Using the 2 filters will enable to you to choose the correct TD dates and to define for which period they are relevant for (since the flag alone isn't defined just for this year or for the previous year).
Image 1. Value of YTD/QTD/MTD flag as 1
Image 2. Time frame filter (This Year, Last Year, This Quarter, 4 Quarters ago etc.)
Attachments
- 11 MB - Dynamic XTD.ecdata
- 29 KB - DynamicXTD.dash
