cancel
Showing results for
Did you mean:
Creator

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

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)

YMSALES TTM

2013-1

4,244,717
2013-24,217,576
2013-34,278,310
2013-44,362,031
2013-54,405,851
2013-64,506,748
2013-74,633,581
2013-84,648,791
2013-94,707,479
2013-104,765,123
2013-114,935,676
2013-124,997,521

How can I do that?

Matan.

1 Solution

Accepted Solutions
Creator
Author

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.

4 Replies
MVP

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

 rangesum(above(Sum({}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.

Creator
Author

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.

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.

talk is cheap, supply exceeds demand
Creator
Author

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.

Community Browser