Product Tour
forums
Use these forums to ask questions and discuss Tableau.
Trailing Twelve Months (TTM) Calculations
Posted April 29th, 2008 by corbinmatthews
Hello,
I am charting some values for which I want to look at trends over the trailing twelve months (TTM). I have created a filter for the TTM as follows:
datepart('year',[Check_Date])>=datepart('year',today())-1
The resulting data appears to be from the last 12 months. The problem now is that, when the months are sorted (manually, I might add, which is quite frustrating) to logically represent the TTM -- i.e. with the most recent month in the data at the far-right of the axis -- the line chart does not update appropriately. Please see the image attached to this post for details.
Please help!
| Attachment | Size |
|---|---|
| temp.PNG | 19.1 KB |
Comments
Won't that calculation give you 16 months: Jan-April this year, and all of last year?
But perhaps I misunderstand your description, because once you shift to a (today() - [Date] < 365) style calc, I would think your "most recent month" would be the current month of April, rather than Jan?
You are precisely right, on both counts. I am now using the following filter:
[Check_Date]>=dateadd('year',-1,today())The other problem that I was having with this chart was as you said -- the most recent month shown on the far right of the x-axis should be March (in this dataset) but it remained January.
It turns out that, to solve the problem, I had to press "Show Me" again and the chart was redrawn properly (after manually sorting the field). Is there a way to have the months automatically sort in the appropriate order for this type of analysis?
How are you displaying the [Check_Date] field in your line graph? If you just use the MONTH(Check_Date) aggregation then Tableau will display in order by Month number only (1-12) and not in chronological order among months in multiple years (i.e. April 2008 will be shown before May 2007 since year is being ignored).
One solution would be to add YEAR(Check_Date) at a higher level than MONTH(Check_Date) so that it can be first sorted by Year, then by month.
Another solution would be to MY(Check_Date) aggregation (identified in the field menu as More > 'MMMM YYYY').
In either event the default ascending sort should create a correct chronological timeline.
Brilliant! Thank you!