Topics started by 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 FilterIn 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 calculationUsing the field you’re measuring, here’s the formula:sum(MEASURE-FIELD-HERE})/12You 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 ho
Already have an account? Login
Login to the community
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.