Setting up a moving annual total figure

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. You can read more about MAT at http://www.pmlive.com/intelligence/healthcare_glossary_211509/Terms/m/moving_annual_total_mat.

Getting ready

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...