
1. Re: Counting (bin) customers by sum of sales per year
Alex Kerin Oct 10, 2013 5:36 AM (in response to darrennorris)Can you share a tbwx with some sample data and expected result?

2. Re: Counting (bin) customers by sum of sales per year
darrennorris Oct 11, 2013 5:31 AM (in response to Alex Kerin)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 NovDec and 90 in JanMar
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 NovDec and 30 in JanMar
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 050, 51100, 101150 etc. I would expect to see a count of 1 (customer A) in 51100 for 2012 and a count of 1 in 51100 for 2013. For customer B I would expect to see a count of 1 in 050 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
Matt Lutton Oct 11, 2013 8:09 AM (in response to darrennorris)Please see: http://community.tableau.com/docs/DOC1251
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
Ramon Martinez Oct 11, 2013 12:00 PM (in response to darrennorris)1 of 1 people found this helpfuldarrrennorris,
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 "050"
ELSEIF SUM([volumen])<=100 Then "51100"
ELSEIF SUM([volumen])<=150 Then "101150"
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

5. Re: Re: Counting (bin) customers by sum of sales per year
darrennorris Oct 14, 2013 12:28 AM (in response to Alex Kerin)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 050 range and 2 (Joe and Steve) customers in the 50100 range. For 2013, I have 1 customer (Fred) in the 050 range and 2 customers (Joe and Steve) in the 100150 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.

Sample workbook.twbx.zip 15.5 KB


6. Re: Re: Re: Counting (bin) customers by sum of sales per year
Matt Lutton Oct 14, 2013 7:08 AM (in response to darrennorris)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 deselect 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 solutiondid I miss anything here, Jonathan?
Feel free to ask questions, if I cannot explain it, Jonathan will be able to.

Sample workbook ML.twbx.zip 21.2 KB


7. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year
Jonathan Drummey Oct 14, 2013 11:42 AM (in response to Matt Lutton)Matt  you didn't miss anything that I could see.
One thing to know about bins is that they are rowlevel 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

Sample workbook ML jtd.twbx.zip 21.3 KB


8. Re: Re: Re: Re: Counting (bin) customers by sum of sales per year
Matt Lutton Oct 14, 2013 11:45 AM (in response to Jonathan Drummey)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
Shawn Wallwork Oct 14, 2013 12:00 PM (in response to Matt Lutton)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
Matt Lutton Oct 14, 2013 12:02 PM (in response to Shawn Wallwork)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
Shawn Wallwork Oct 14, 2013 1:01 PM (in response to Matt Lutton) 
12. Re: Counting (bin) customers by sum of sales per year
Matt Lutton Oct 14, 2013 1:04 PM (in response to Shawn Wallwork)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
Shawn Wallwork Oct 14, 2013 1:12 PM (in response to Matt Lutton)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 & WellDeserved!
Shawn

14. Re: Counting (bin) customers by sum of sales per year
Jonathan Drummey Oct 14, 2013 1:25 PM (in response to Matt Lutton)Me too!