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.
A proper data modeling will help you answer this kinds of questions with Month to Date (MTD), Quarter to Date (QTD), and Year to Date (YTD) calculations. Month, Quarter and Year to Date values show results of an activity between a period starting from the beginning of the current Month, Quarter and Year and ending at the current date (exclusive, since this day may not yet be "complete"). In addition, note the following:
- Quarter to date (QTD) - records results of an activity to the beginning of either the calendar or fiscal Quarter.
- Year to date (YTD) - Year to date is a period starting January 1 of the current year and ending today. However in business settings, companies may have fiscal years begin at other times.
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
Calculate MTD, QTD, YTD: Elasticube Application
Open the Elasticube cube you are working with and navigate to your date dimension. In your dimDates you should define flags – Boolean fields – that define whether or not a specific date should be included in the calculation.
This, can be done by creating custom field that tests whether the date falls before today’s date or not. Note that this flag will be useful also for dates belonging to other periods than the current. The value of the flag will be 1 for dates in the beginning of the period irrespective of which period it is.
Step 1 -
Create a Custom SQL Expression or extend your existing dimDates table with the following Syntax:
SELECT *, CASE WHEN [thisQTRIndex]>=[QTRIndex] THEN 1 ELSE 0 END isQTD, CASE WHEN [thisYTDIndex]>=[YTDIndex] THEN 1 ELSE 0 END isYTD, CASE WHEN [thisMonthDay]>=[monthDay] THEN 1 ELSE 0 END isMTD FROM (SELECT *, [MonthIndex]*100+getday(Date) QTRIndex, [thisMonthIndex]*100+getday(now()) thisQTRIndex, getmonth(Date)*100+getday(Date) AS YTDIndex, getmonth(now())*100+getday(now()) AS thisYTDIndex, getday(Date) monthDay, getday(now()) thisMonthDay from (SELECT DISTINCT Date, toint(CASE WHEN tostring(getmonth([Date])) IN ('1','4','7','10') THEN '1' WHEN tostring(getmonth([Date])) IN ('2','5','8','11') THEN '2' WHEN tostring(getmonth([Date])) IN ('3','6','9','12') THEN '3' END ) MonthIndex, toint(CASE WHEN tostring(getmonth(now())) IN ('1','4','7','10') THEN '1' WHEN tostring(getmonth(now())) IN ('2','5','8','11') THEN '2' WHEN tostring(getmonth(now())) IN ('3','6','9','12') THEN '3' END ) thisMonthIndex FROM dimDates ) dimDates1 ) dimDates2
Step 2 -
Link the new Custom table to the relevant Transaction Date and mark all the fields you don’t with to display in the Web application as “invisible”:
Step 3 -
Add the isMTD / isQTD / isYDS =1 flag as a “measured value” within the formula editor (assist this documentation):
Step 4 - Results
The YTD / QTD / MTD flags define whether or not a specific date should be included in the value calculation: