3 Replies Latest reply on Sep 26, 2016 9:46 AM by victor chan

    Bucket counting problem...

    Eric Tobias

      I am trying to classify a customer's monthly order volume as "small", "medium", and "large" and then count the unique customers in the bucket. So for example, if a customer's total monthly order quantity is less than 10, it is "small"; 10 to less than 20, it is "medium"; and 20 or larger, it is "large". Once I know each customer's classification in any given product and month I want to count them.

       

      In the attached workbook I have extracted Superstore data. In January 2011, for the "Office Supplies" category I have 26 unique customers, of which one is large, five are medium, and 20 are large. I'm sure I'm overthinking this, but I can't get the right answer. For my results I'd like the month and category as rows and the bucket as columns and a distinct count of customers as values. I've tried table calculations, LOD expressions, and basic aggregation, but I can't get the math to work out. I run into a variety of issues trying to use the "Quantity Bucket" calculated field in either an LOD or a table calc.

       

      Hopefully someone can look at this, laugh and point out the obvious mistake I'm making, but I've tried numerous ways and always run into a dead end.