TTM \ LTM by Year \ Year-Month \ Year-Quarter ...

[matancha]

Hello all.

I have a sales table by dates, and a calender with Year, YM, and YQ fields.

The tables are connected by 'DateKey' field.

I want to show the TTM (Trailing twelve months) by months.

I have the expression: Sum({<Year=, YM =, YQ=,Date = {">=$(=vTTM_StartDate)<=$(=vTTM_EndDate)"} >}Sales)

ie, for example, if the user select the year 2013, in the "Sales TTM" table - i want to show:

(the TTM - for each month)

YM	SALES TTM
2013-1	4,244,717
2013-2	4,217,576
2013-3	4,278,310
2013-4	4,362,031
2013-5	4,405,851
2013-6	4,506,748
2013-7	4,633,581
2013-8	4,648,791
2013-9	4,707,479
2013-10	4,765,123
2013-11	4,935,676
2013-12	4,997,521

How can I do that?

Many thanks in advance....

Matan.

[Nicole-Smith]

The expression you need is this (file also attached):

rangesum(above(Sum({<Year=, YM =, YQ=>}Sales),0,12))

The only problem is, it doesn't let you filter the chart with this expression.  Maybe someone else can chime in to clean that part up.

[matancha]

Thank you Nicole,

But besides the fact that it doesn't let me filter, I use a cyclic dimension - and if i change from YM to YQ or Year - I get incorrect results.

Maybe there is another approach?

Thanks again -

Matan.

[Gysbert_Wassenaar]

I think you're best served by creating an AsOf table. See this document for an explanation: Calculating rolling n-period totals, averages or other aggregations.

[matancha · Accepted solution]

Gysbert - Thank you very very much!!!

AsOf table approach helped me a lot!

I'm attaching the my final example file.

I'm hope this is clear enough...

Thank you again -

Matan.