A moving annual total (MAT) is the total value of a variable, such as sales figures for a product, over the course of the previous 12 months. This is a rolling yearly sum, so it changes at the end of each month with data from the new month added to the total and data from the first month of the period taken away read more about moving annual total (MAT), at http://www.pmlive.com/intelligence/healthcare_glossary_211509/Terms/m/moving_annual_total_mat.
We are going to make use of variables in this recipe. We will define three variables in the script: vMonthFormat, vRolling12Months, and vMaxMonth.
Load the following script into your Qlik Sense application:
LET vMonthFormat = 'MMM-YYYY'; LET v12MonthsBack = 'Date(AddMonths(max([MonthYear]), - 12),$(vMonthFormat))'; LET vMaxMonth='Date(max([MonthYear]),$(vMonthFormat))'; Sales: LOAD Date(Date#(MonthYear, 'MMMYYYY'), 'MMM-YYYY') as MonthYear, Month(Date#(MonthYear, 'MMMYYYY')) as Month, Year(Date#(MonthYear, 'MMMYYYY')) as Year, Sales INLINE [ MonthYear, Sales Jan2014, 1000 Feb2014, 1520 Mar2014, 1600 Apr2014, 3000 May2014, 2500 Jun2014, 4500 Jul2014, 6000 Aug2014, 6500 Sep2014, 7800 Oct2014, 6800 Nov2014, 3000 Dec2014, 2500 Jan2015, 750 Feb2015, 1200 Mar2015, 800 Apr2015, 600 May2015, 2100 Jun2015, 3500 Jul2015, 4700 Aug2015, 2100 Sep2015, 3500 Oct2015, 4700 ]; FOR vMonth = 0 to 11 MATMonthYear: LOAD [MonthYear], Date(AddMonths([MonthYear], $(vMonth)),'$(vMonthFormat)') as [MAT MonthYear] RESIDENT Sales WHERE AddMonths([MonthYear], $(vMonth)) < today() ; next
- Once the data is loaded, open the App overview window and create a new sheet.
- Enter the Edit mode by clicking on
. - Drag across the
object from the Assets panel on the sheet. - Name it
Moving Annual Total. - Add
[MAT MonthYear]as the dimension. - Next, add the following measure and label it
MAT Sales:SUM({<[MAT MonthYear]={">=$(vRolling12Months)<= $(vMaxMonth)"}>}Sales) - Save the application and click on
. - Under Appearance, select the chart style as
. - Check the Show Data points.
- Switch on the Value Labels options to show values on each data point.
- The final trendline chart should look like this:
The Moving Annual Total curve helps in smoothing out the spikes that occur in a single month by making use of the annual totals. This is achieved by calculating the rolling 12 months accumulated sales data for each data point.
We create a MATMonthYear field. You will notice that when we select any month and year value in this field, it associates the field value to the current MonthYear and the 11 MonthYears prior to the current, in the MonthYear field.
In the MAT Sales expression, we make sure that the rolling 12 months are always shown in the chart. This is achieved by restricting the MATMonthYear values shown in the chart between the vRolling12Months and the vMaxMonth variables.
Selecting any MATMonthYear will result in the trendline chart populating the MAT figures for the selected month and 11 months prior to that.
There is a similar concept known as cumulative sums, which we discussed in the recipe Using the Rangesum() function to plot cumulative figures in trendline charts. However, there is a glaring difference between the two. While cumulative takes into consideration all the previous months and years to the current, a Moving Annual Total will always consider the previous 12 months. In a way it is a rolling 12 month sum at any given point of time.