
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
