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

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

    J-P Gauthier



      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!