How to Calculate 12-Month Trailing Average (TTA) in a Look

[Scott_Bennett]

I’ve figured out how to calculate a 12-month trailing average (TTA) in a look for the purpose of understanding trends.  Here’s how I did it.  It requires the use of a custom filter to select the right data and then a table calculation to get the result.

Step 1 - Custom Filter

In your new Look, create a Custom Filter to select the 12 months previous to the last completed month.  Using a date-month field, here’s the formula:

MONTH-DATE-FIELD-HERE<trunc_months(add_months(-1,now()))AND MONTH-DATE-FIELD-HERE>trunc_months(add_months(-14,now()))

Depending on the field you’re using, you may need to use -13 instead of -14 to get 12 months.  Validate that by looking at your data by month.

Step 2 - Add a table calculation

Using the field you’re measuring, here’s the formula:

sum(MEASURE-FIELD-HERE})/12

You can format the field so you don’t get long decimal points in your result.

On your other measure, select “Hide in Visualization” and change the Visualization to Single Value to display the TTA.

I hope this helps.  Please comment if you have further suggestions.