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

# Bucket counting problem...

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.

• ###### 1. Re: Bucket counting problem...

Hey Eric,

Is something like this what you are looking for?

I wrapped the Bucket calc in a Fixed LoD and then ran a countd on customerID.

- Derrick

2 of 2 people found this helpful
• ###### 2. Re: Bucket counting problem...

And there it is... as simple as it should have been. Not sure where my thought process derailed, but your solution works great. Thanks!

• ###### 3. Re: Bucket counting problem...

You're a real life saver, you know that?