4 Replies Latest reply on Sep 22, 2016 8:39 AM by J-P Gauthier

# Combining Last Twelve Months (LTM / TTM) and Customer Bucketing

Hi,

Hope all is well. I am trying to include a Last Twelve Months calculation in my Customer Groupings formula but I am unsure how to accomplish this. A Tableau packaged workbook is attached. There are currently 3 tabs:

1) Customer Size by Customer

We are classifying customers into three buckets: small, medium and large. As the customer size formula is currently built, we fixed it to the customer name (so it calculates at the customer-level) and a custom year date (so it restarts calculating every year). We then sum the sales of a customer within that year and if they are below medium min (a parameter), the customer gets classified as small for that year. If they are below large min, they are medium, and if they hit large min or above, they are large.

2) LTM Sales by Customer

We are calculating the last twelve months sales by customer using a running sum. The specific formula is a window_sum (WINDOW_SUM(SUM([Sales]), -11, 0) achieved through a simple table calculation.

3) Desired Outcome

We would like to see Last Twelve Months sales by customer size on a monthly basis. Specifically, we need to adjust the Customer Size calculated field so that it classifies a customer as small, medium or large monthly based on each customer's LTM sales (as in tab 2) rather than its yearly sales as it is currently built (i.e. if Customer 1 has sales > large min in February 2014, he would be Large for the next 12 months, but in February 2015, that order would drop off the calculation since it is 13 months later and if it had no further orders, Customer 1 would fall into the small group).

The specific layout we would like is customer size on the left, months on the top and the values inside the table should be LTM Sales. Any thoughts would be greatly appreciated!

• ###### 1. Re: Combining Last Twelve Months (LTM / TTM) and Customer Bucketing

Hi J-P (Jean-Paul i guess :-),

This is the moving window table calculation

that complicates matters a lot.

Grouping (Binning) the results of table calculation(s)

should be done via table calculation(s). This is by definition.

Summing up moving LTM Sales is another table calc

on top of (table calc) bins. So it quickly becomes complicated.

And of course Customer Name should be on Detail --

this is required for the table calculations to take place.

And it also causes the stacked Marks on a view,

so one should turn them OFF to get a proper table layout.

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Combining Last Twelve Months (LTM / TTM) and Customer Bucketing

Yuri,

Thanks so much, that was not an easy one! Last quick question: do you know why the numbers show up like this when I tried to replicate your analysis on my side? They weirdly seem bolded even though I didn't select that feature

• ###### 3. Re: Combining Last Twelve Months (LTM / TTM) and Customer Bucketing

Hi J-P,

This would be because of the presence

of other Dimension Pills on a view.

There are only [Customer Name] and [Date] dims

on the Sheet 7 of the Sample Tableau Workbook.

But there may be other dim pills on your original view as well.

Stacking Marks OFF place Text Marks on top of each other,

thus making text look bold & jaggy.

Yours,

Yuri

• ###### 4. Re: Combining Last Twelve Months (LTM / TTM) and Customer Bucketing

Perfect, thanks for the explanation!

J-P