1 2 Previous Next 18 Replies Latest reply on Oct 15, 2013 6:03 AM by darrennorris

# Counting (bin) customers by sum of sales per year

Hi all

I have a problem that I can't quite work out how to solve.

I have a table with the following data:

Customer name

Product (i.e. the product they have purchased)

Date

Volume (i.e. the quantity of products purchased)

I want to be able to group customers in to groups (bins) by the volume of products purchased over a year, e.g. under 100, 101-250, 251-500 etc.

However the results I'm getting using Create Bins seems to be grouping the data at individual transaction level rather than summing customers by their total activity over the time period and then grouping them.

e.g. if customer X made four separate orders over the year for 50 items at a time, I would expect that customer to fall within the 101-250 bin and be counted once. The results I'm getting, however, seem to be counting the same customer four times within the under 100 bin.

Hope this makes sense and appreciate your help.

• ###### 1. Re: Counting (bin) customers by sum of sales per year

Can you share a tbwx with some sample data and expected result?

• ###### 2. Re: Counting (bin) customers by sum of sales per year

Hi. I'm not sure how to share/upload a sample workbook. For now let me try to explain.

Let's imagine i have 10 rows of data made up of two customers (Customer A and Customer B)

Customer A purchased Product X in November (2012), December (2012), January (2013), February (2013), March (2013). Each month they ordered 30 items. Thus 150 items in total but over two calendar years. 60 in Nov-Dec and 90 in Jan-Mar

Customer B purchased Product Y in November (2012), December (2012), January (2013), February (2013), March (2013). Each month they ordered 10 items. Thus 50 items in total but over two calendar years. 20 in Nov-Dec and 30 in Jan-Mar

Whilst I can easily create a table that sums the customer's total orders by calendar year, I want to be able to count the number of customers that have total orders within a range/bin by calendar year.

For example, if my ranges/bins were 0-50, 51-100, 101-150 etc. I would expect to see a count of 1 (customer A) in 51-100 for 2012 and a count of 1 in 51-100 for 2013. For customer B I would expect to see a count of 1 in 0-50 for 2012 and 2013.

Essentially I'm trying to see whether I have more customers making high or lower volume orders per year.

• ###### 3. Re: Counting (bin) customers by sum of sales per year

You should be able to mock up a similar scenario using the sample Superstore data, post a packaged workbook (.twbx) and someone here can help show you how to do what you are after.

• ###### 4. Re: Re: Counting (bin) customers by sum of sales per year

darrrennorris,

I tried to recreate your scenario with the following data set:

Customer name,Product,date,volumen

A,X,11/01/2012,30

A,X,12/01/2012,30

A,X,01/01/2013,30

A,X,02/01/2013,30

A,X,13/01/2013,30

B,Y,11/01/2012,10

B,Y,12/01/2012,10

B,Y,01/01/2013,10

B,Y,02/01/2013,10

B,Y,03/01/2013,10

Use this steps:

1.- Create a calculated field: Volume Custom Bins

with the following formula

IF SUM([volumen])<=50 Then "0-50"

ELSEIF SUM([volumen])<=100 Then "51-100"

ELSEIF SUM([volumen])<=150 Then "101-150"

ELSE "150+"

END

Put the field volumen on Columns

Put Year(date) and the calculated field "Volume custom bins" on Rows

I think that I get what you need.

I'm adding the workbook I created in order you have the implementation.

I hope this help

1 of 1 people found this helpful
• ###### 5. Re: Re: Counting (bin) customers by sum of sales per year

Thanks all for the replies, incl. Ramon.

Apologies for not attaching a sample workbook earlier. Now attached.

In the Data tab of the attached workbook, you can see that for 2012 I have 1 customer (Fred) in the 0-50 range and 2 (Joe and Steve) customers in the 50-100 range. For 2013, I have 1 customer (Fred) in the 0-50 range and 2 customers (Joe and Steve) in the 100-150 range.

I am trying to produce a table that simply counts the number of customers based on the sum of their orders. In the Bins tab, it seems it is counting the number of customers at order level rather than summing their orders.

Essentially I'm trying to count the number of customers I have within specific order volume ranges, i.e. how many customers do i have that place more than X orders per year.

• ###### 6. Re: Re: Re: Counting (bin) customers by sum of sales per year

Attached is a possible solution.  I used the calculation described by Ramon, as well as a "# of Customers" calculation that is "IF FIRST==0 THEN SIZE() END", set to Compute Using "Customer", which is on the level of detail in the bar chart and crosstab view.  On the blue AGG (Order Volume Groups) pill, I right clicked to de-select the "ignore in Table Calculations" option so the groups are part of the view's partitioning.  Thanks to Jonathan Drummey for helping me figure out this type of solution--did I miss anything here, Jonathan?

Feel free to ask questions, if I cannot explain it, Jonathan will be able to.

• ###### 7. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year

Matt - you didn't miss anything that I could see.

One thing to know about bins is that they are row-level calculations, so each record in the data source is fit into the appropriate bin, then that partitions whatever the measure is. In the Bins worksheet, the data doesn't have much year to year variation so the COUNTD(Customer) returns the same number of customers for each year in each bin.

When you want to count or sum some value per customer, then we're going to need customer in the view to aggregate at the customer level, then some more calcs to aggregate the customers, and that's what Matt created. The nice part about this is that you can partition the view on whatever you like (such as the Year) to get the count of customers in each partition.

You first talked about wanting to identify specific order volume ranges, then mentioned wanting to find out how many customers who place more than X orders in a year. An easier way to get at the latter with a Set which effectively gives you two partitions (who is in or out of the set), I created that in the attached.

Jonathan

1 of 1 people found this helpful
• ###### 8. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year

Excellent.  I am really starting to feel and see the benefits of my studies/trolling the forum.  Appreciate all the help thus far!

• ###### 9. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year

Ahhh, Matt I'm not sure 'trolling' is what you're looking for here; 'cause you ain't and you don't.

--Shawn

• ###### 10. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year

I'm just a step above the trolls, really   But as you probably know, I spend so much time here because I've learned more about Tableau from this forum than from anywhere else.

• ###### 11. Re: Counting (bin) customers by sum of sales per year

Now that you've made the Top 10 all-time board, I'm thinking a bit of profile editing is in order:

--Shawn

• ###### 12. Re: Counting (bin) customers by sum of sales per year

Yeah, but we both know my numbers are inflated from a lot of posts that weren't "correct" or helpful in any way...  but, I appreciate the suggestion and will edit.

• ###### 13. Re: Counting (bin) customers by sum of sales per year

Matthew Lutton wrote:

Yeah, but we both know my numbers are inflated from a lot of posts that weren't "correct" or helpful in any way...

Ha! That describes at least 10K of my near 20K points. Effort is effort at whatever level, and is greatly appreciated. For a long time I was happy to be the "you posted a twb instead of a twbx" guy, and hence racked up an inordinate amount of points. I'm happy you've taken up up the "could you please post a sample workbook" cause because I finally burned myself out on that one; but am glad this important/vital role is not going unfulfilled.

Matthew, this is my way of giving you a big Thank You, Shout Out & Well-Deserved!

--Shawn

• ###### 14. Re: Counting (bin) customers by sum of sales per year

Me too!

1 2 Previous Next