Can you share a tbwx with some sample data and expected result?
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.
Please see: http://community.tableau.com/docs/DOC-1251
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.
1 of 1 people found this helpful
I tried to recreate your scenario with the following data set:
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"
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
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.
Sample workbook.twbx.zip 15.5 KB
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.
Sample workbook ML.twbx.zip 21.2 KB
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.
Sample workbook ML jtd.twbx.zip 21.3 KB
Excellent. I am really starting to feel and see the benefits of my studies/trolling the forum. Appreciate all the help thus far!
Ahhh, Matt I'm not sure 'trolling' is what you're looking for here; 'cause you ain't and you don't.
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.
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.
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!