Calculating Moving Annual Target (MAT) with SAP BusinessObjects 4.x

INTRODUCTION

Moving Annual Target or the Moving Annual Total (MAT) is calculated as the Actual value of the variable based on the running sum of the previous 12 months. Each month (or end of the month) is an indicator of adjusting the MAT value to include a new month and exclude the last month from the previous 12 months.

A business requirement prompted us to create the MAT logic in SAP BusinessObjects BI 4.1 SP6 (Web Intelligence) based on the user selected Review Start Date and End Date, for a customer at the lowest granular level in the hierarchy.

In a typical business scenario, a business user is prompted to select the Customer, Enter Review Start Date and the Review End Date to analyse the financial data for that customer. The Review End date marks as an indicator for the MAT calculation for the selected customer.

EXAMPLE

  • Prompt Selection – Customer A
  • Review Start Period – 05.2013 (MM.yyyy)
  • Review End Period – 07.2015 (MM.yyyy)
  • MAT in this case will be calculated for 07.2015 (as Year 1 from 08.2014 to 07.2015)
  • MAT for the previous year 07.2014 (as Year 2 from 08.2013 to 07.2014)
  • MAT for 07.2013 will not be taken into account in this case as the Review Start Period begins from 05.2013 and wouldn’t have the entire data set of the previous 12 months to complete the calculation. MAT 07.2013 may give incorrect results with data rolling for just 3 months.

APPROACH

In this example we will be calculating MAT against the Contract Review Date. First, let’s calculate the Running Sum of Sales Volume with the Contract Review date in the ascending order.

=RunningSum([Sales Volume])

We also need to calculate the [Contract Review Period Date] and [Contract Review End Date]

  • [Contract Review Date] – All months between the Review Start Period and Review End Period (in Date format)
  • [Contract Review End Date] – Last Month in Contract Review Period. It is the same as Review End Period (in Date format)

 

As we need the data for previous 12 months, subtract the running sum of the previous 12 months from this calculation. We will refer to this variable as [Rolling 12 Months]

=RunningSum([Sales Volume]) - RunningSum(Previous([Sales Volume];12))


MAT in the final report layout has to be displayed only for the Contract Review End Date and its Relative date (last year) as highlighted in the section below. If you are on BusinessObjects BI 4.1 SP6 or later version, calculating Relative Date last year (or last week or last month) will be relatively straightforward. This is thanks to the new changes made in the RelativeDate() function in SP6 release – really simple!

RelativeDate([Contract Review End Date];-1;YearPeriod)

 

To get this information, we will create a new variable to restrict the data only for the MAT period we require in the report. Let’s refer to this variable as [Reference MAT]


= ([Rolling 12 Months] Where (([Contract Review Date] = [Contract Review End Date]) Or ([Contract Review Period Date] = [Relative Date 1]) Or ([Contract Review Period Date] = [Relative Date 2]) Or ([Contract Review Period Date] = [Relative Date 3]) Or ([Contract Review Period Date] = [Relative Date 4]) Or ([Contract Review Period Date] = [Relative Date 5])) ) ForEach ([Contract Review End Date])

The RelativeDate() in the above formulae depends on the maximum number of years of retrospective data we require for our analysis. In this case we have restricted the MAT calculation for a maximum period of 5 years.

 

Now that we have achieved our results, it’s time to filter these rows from the report. Create a variable to count the number of rows in this table.

=RunningCount([Contract Review Period Date];Col) In ([Contract Review Period Date])

At this time, we have a count of all the rows, but do you need the count for all the rows in this table? Maybe not. Restrict the count variable with an additional condition, where the [Reference MAT] values are greater than zero.

=(RunningCount([Contract Review Period Date];Col) In ([Contract Review Period Date]) ) where ([Reference MAT] > 0)

Apply the Filter on the table with [Row count] variable greater than or equal to 1.

 

This filter returns only those rows where Reference MAT is greater than 0. We have now successfully restricted the data for the given Reference date, however you will notice that the MAT values are incorrect.

 

Now, go back to the original calculation for [Rolling 12 Months] and change the variable to include:

=NoFilter((RunningSum([Sales Volume]) - RunningSum(Previous([Sales Volume];12))))

 

Format the data based on the requirements in a chart or a cross tab format.

 

In the above example we aggregated the data to calculate MAT against the time dimension, however if more than one dimension is added to the table, the MAT calculation has to be redesigned to include these dimensions in the context. Let’s take the same example and split the data against Product and Time dimensions. Insert a cross tab in the report layout and include [Contract Review Date], [Product] dimensions. Then calculate the MAT in the report (similar to the process defined earlier).

=RunningSum([Sales Volume];Row;([Product])) - RunningSum(Previous([Sales Volume];Row;12);Row;([Product])) ForEach ([Product])

You may notice that, in the calculation above we have set the Running Sum calculation row wise and included [Product] as the reset dimension. For each row and a new Product, the running sum will be calculated across the date dimension. If a new dimension is added to the report layout, then the dimension needs to be included as a part of the calculation context. For example: New [Customer] dimension added to the cross tab

=RunningSum([Sales Volume];Row;([Product];[Customer])) - RunningSum(Previous([Sales Volume];Row;12);Row;([Product];[Customer])) ForEach ([Product];[Customer])

If the dimensions need to be swapped, the formulae needs to be adjusted accordingly:

=RunningSum([Sales Volume];Col;([Contract Review Period Date])) - RunningSum(Previous([Sales Volume];Col;12);Col;([Contract Review Period Date])) ForEach ([Contract Review Period Date])

 

MAT in the final report layout is required only for the Contract Review End Date and its Relative Date (last year). To get the required data, we will restrict the calculation based on a condition.


Sum (If ([Contract Review Period Date] = [Contract Review Period End Date]) Then (RunningSum([Sales Volume];Row;([Product])) - RunningSum(Previous([Sales Volume];Row;12);Row;([Product])) ForEach ([Product])) else 0

The above calculation returns the results where the date criteria matches in the report. Now, insert Sum() at Row / Column level. Sum() in this case will only be the Sum() of the columns which match the date criteria. You may further choose to exclude the columns with 0 or NULL values and display only the Total column as MAT final output.

 

Format the report based on the business requirements.

 

Sandra Cutic