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.
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 Today
QTD – From beginning of the Quarter until Today
MTD – From beginning of the Month until 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)
- MTD, QTD, YTD: Working with Data Sets Webinar
- Custom Month and Year to Date to Last Year MTD and QTD Calculations
- Creating Fiscal Date Fields
When the quick functions in the dashboard do not answer this need, we will need to model it in the cube.
Define new flags in the Dim date table (either as a new field or in the custom query)
- YTD - if we are less than today's month then it is YTD, if we are in today's month and the day of the month is smaller or equal to today then it is YTD. Otherwise it is not.
- MTD – if we are 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.
Dim date table is aliased as d
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 isYTD
CASE WHEN getday(d.Date) <= getday(now()) THEN 1 ELSE 0 END AS isMTD
For each measure you use, add a widget filter/measured value with:
- Value of isYTD / isQTD / isMTD flag as 1
- Time frame filter (This Year, Last Year, This Quarter, 4 Quarters ago etc.)
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.)