-
1. Re: Calculate average of values within nested bins
Alex Kerin Jan 16, 2013 5:43 PM (in response to Jay Chang)Where are your bins coming from - do they exist in the datasource or are you calculating them?
Edit: Doesn't matter - you can use the bins created in Tableau to partition the window_avg calculation. See attached. How you end up showing this is a different matter...
-
avg of bins.twbx.zip 1.1 MB
-
-
2. Re: Calculate average of values within nested bins
Jay Chang Jan 16, 2013 5:39 PM (in response to Alex Kerin)I used the Tableau bin feature to slot my data. I calculated the years of service within tableau as well. My years of service calculation basically takes the difference between 2013 and whatever the year value is in an EE's service date. So they are all integers. Hourly rate came from an external source that I pulled into Excel. I created a datasource in Tableau to my Excel data.
-
3. Re: Calculate average of values within nested bins
Alex Kerin Jan 16, 2013 5:43 PM (in response to Jay Chang)I edited my answer above
-
4. Re: Calculate average of values within nested bins
Jay Chang Jan 16, 2013 5:58 PM (in response to Alex Kerin)I do not think this is working as I would like. I see you are using only one bin (Sales), but Customer Segment is a Dimension, rather than a bin. An analogy using your data would be to create bins on Sales and Profit, replace the customer segment pill with the Profit (bin) pill, and then calculate an average profit within each sales bin.
of course if I'm mis-interpreting your solution, then perhaps I'm not nearly as good with Tableau as I think I am.
-
5. Re: Calculate average of values within nested bins
Jay Chang Jan 16, 2013 6:11 PM (in response to Jay Chang)To clarify: using your technique, I can get some data to match my expectations.
for example, when I use the following formula:
window_sum(sum[hourly rate])
computed using pane (across then down), I get the sum of all hourly rates within the years of service bin, just as I expect. but when I change the formula to either:
window_avg(sum([hourly rate]) or window_avg(avg([hourly rate])
I don't get answers anywhere close to what I'd expect.
-
6. Re: Calculate average of values within nested bins
Alex Kerin Jan 17, 2013 5:06 AM (in response to Jay Chang)I can't help any more then without a tbwx (save as) to see your setup. If your data is sensitive, see if you can use the superstore demo database to create an equivalent.
-
7. Re: Calculate average of values within nested bins
Shawn Wallwork Jan 17, 2013 5:15 AM (in response to Alex Kerin)Jay what Alex said. But if you decide to go it alone, here's a good place to start:
--Shawn
-
8. Re: Calculate average of values within nested bins
Jay Chang Jan 17, 2013 8:17 AM (in response to Shawn Wallwork)I've attached a packaged workbook using storefront that has a layout somewhat like what I've created in my own data.
Note that I've created a Sales bin that segments sales into groups by 15K intervals and that I've created a Profit bin that segments Profits by $500 intervals. I'm showing counts of how many rows have profits (by bin) within sales (by bin).
I'm trying to calculate average profit across the profit bins within each sales bin. Sheet 2 has the profit value I'm trying to get to within each sales bin. I'm wondering if there is some way to calculate the values in sheet 2 using the viz showing in sheet 1.
Hopefully this makes more sense?
-
example.twbx.zip 1.1 MB
-
-
9. Re: Calculate average of values within nested bins
Alex Kerin Jan 17, 2013 11:14 AM (in response to Jay Chang)Great example - thanks.
The way to get to your calculation is to use total(avg([Profit]) and set it to only use the sales bins and ignore the profit bin.
The easiest way to do this is using a reference line (as you don't even have to mess with table calcs, just make avg(profit) available on the level of detail, and set the reference line to it, and choose total from the drop down:see the first sheet
Or in the second, I've explicitly created this total(avg.. and plotted it on a double axis, synced the axes, set it to compute to Profit (bin) (counter intuitively to me, this effectively means ignore the profit bins when calculating the average). Finally I've used multiple marks to change the avg to a line. see the second sheet
EDIT: of course there's no good reason the sync the axes - in this case the numbers work out to be in a similar range. You may want to look at different ways of showing the average on the chart, but at least you have the calc to do it now.
-
example AK.twbx.zip 1.1 MB
-
-
10. Re: Calculate average of values within nested bins
Jay Chang Jan 18, 2013 10:11 AM (in response to Alex Kerin)Hi Alex - sorry for the delayed response, I was swamped yesterday. I implemented the formula as you describe, applying it to my situation and it is not producing the result I expect. For whatever reason, I am still not getting an average hourly rate that matches what I calculate in Excel. It's a very confusing thing. Unfortunately I can't post my data, and perhaps I did not accurately create the analagous sitatuion in storefront, but the expected results do not jibe.
In looking over the formula, I had an overall question - why use TOTAL of AVG? I would have thought that this would give me the average for each sub-bin within Profit and then totaled those averages within each Sales bin. Am I not understanding that formula correctly?
-
11. Re: Calculate average of values within nested bins
Alex Kerin Jan 18, 2013 10:19 AM (in response to Jay Chang)You need to look carefully at the way that I have partitioned the table calculation - see the images above.
Total does not sum, it completes the aggregation (avg in this case) across whatever window we specify.
I can't help any more without seeing you data. If it's sensitive create some mock data with the same structure and show us where the results differ from Excel.
-
12. Re: Calculate average of values within nested bins
Jay Chang Jan 18, 2013 10:26 AM (in response to Alex Kerin)SUCCESS! THANK YOU!
I was using the average from the calculated field, rather than using the Average of my hourly rate field. When I modified to follow what you had done, I got the results I was expecting. THANK YOU.
Now can you please expalin (in very simple terms, cause apparently I'm a doof) exactly why/how this works?
-
13. Re: Calculate average of values within nested bins
Alex Kerin Jan 18, 2013 10:37 AM (in response to Jay Chang)1 of 1 people found this helpfulNo problem. Let's start at the beginning - you understand the general concept of partitioning? You could thing of having 3 discrete dimensions of fruit - type of fruit, color of fruit, shape of fruit.
Then I have a bunch of different fruits and I want to show their various attributes on a chart. But I also want to give an overall average weight (my continuous measure). I may want to do this by the type of fruit (what is the average weight of apples v bananas), color (are green fruits typically heavier than yellow ones?) or shape (are spherical fruits heavier than elongated ones?). These are my three different partitions I could create.
In your case your bins (blue, discrete) are my different attributes. I need to set my partitions up. If I want the average across the profit bins, that needs to be on the left side of advanced compute table calculation dialog. Everything else needs to be on the right.
Then each member of the attribute on the right (e.g. each bin) will have it's own value of total average.
You are not a doofus - understanding the concept and application of table calcs is new thinking for many people. The possibilities, and their complex behavior still eludes me many times.
-
14. Re: Calculate average of values within nested bins
Jay Chang Jan 18, 2013 10:43 AM (in response to Alex Kerin)Thank you. That explanation helped, though I'll still need to work through it in my head to "get it".
Table Calcs and Partitions still remain a total mystery to me at times. I am really hoping that one day someone will offer a course that focuses exclusively on how these things work. Even the advanced course kind of skims over the intricacies of table calcs. Frankly, I see a lot of potential for data processed in Tableau to be mis-interpreted because people are so used to the point-click-drag-drop simplicity of the product that they don't think about the results of the answers Tableau is providing. They may be correct answers but they may not be the "right" answers.