Named Set - Trailing 12 Months TOTAL

­­General Discussions
1

How do I create a total of the trailing 12 months when using a named set for the trailing 12 months?

2

Hi Nicole,

You can create a template calculation. For example, if you want the trailing 12 months for April 2022, you will have to have columns for May 2021-April 2022 in your report and then find the sum within the same report. This works the same way for a named set.

This is something Brian A can assist you with in our support portal during your next call :slightly_smiling_face:

3

Sydney’s suggestion works nicely…

Another possibility is that you can setup a new time perspective member…call it TTM…it would be a calculated type…

In the calculation definition you can enter the following:

SUM(LASTPERIODS(12,[Time].[DEFAULT].CURRENTMEMBER),[Time_Perspective].[DEFAULT].&[BASE])

If you are only looking for TTM for an account or two you can use the LAG function in the Account dimension…here is an example where TTM is calculated for EBITDA:

[EBITDA]+LAG([EBITDA],1)+LAG([EBITDA],2)+LAG([EBITDA],3)+LAG([EBITDA],4)+LAG([EBITDA],5)+LAG([EBITDA],6)+LAG([EBITDA],7)+LAG([EBITDA],8)+LAG([EBITDA],9)+LAG([EBITDA],10)+LAG([EBITDA],11)

There are others on here that would have even BETTER ways than the ones that I just shared but posting just in case it is helpful.

Thanks

Monte

4 Likes
4

Monte’s suggestion using the time perspective Sum formula is the most common way of calculating a TTM. This would allow you to have Time in the pages, and depending on which month you select, you can see the TTM value on the screen (having Time perspective in rows/columns).

1 Like