-
1. Re: Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?
Jonathan Drummey
Feb 19, 2013 1:38 PM
1 of 1 people found this helpfulHi Lynn,
What you're looking for in Tableau are table calculations. In the attached I set up answers to both your questions, in the first case I used the Quick Table Calculation "% of Total" with a Compute Using of the Product Purchased (so it partitions on the Ticket #). In the second case I set up a table calc using the WINDOW_AVG function with a Compute Using of the Month(Date) so it partitions for each QUARTER(Date).
Here's a link to a set of tutorials on table calculations:
http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/
And here are a couple of other links from recent forum threads on users going from SUMIF() to Tableau-land:
http://community.tableau.com/thread/122773
http://community.tableau.com/thread/122726?start=0&tstart=0
Jonathan
-
2. Re: Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?
This was very helpful. Thank you.
For part 2 of the original question, your example provided the desired result, but I find it to be a little complicated and wonder if there is an easier way. I have a follow up to that question and am providing new data (with more records).
The attached Excel file provides lbs, sales $s, and gross $s for transactions.
Goal: Calculate and Graph Averages per Month by Year
It is easy to calculate Totals by Month & Year in Tableau.
In order to calculate Averages per Month by Year, I need to...
(1) create a calculated field for each Measure with an IF statement using the WINDOW_AVG table calculation function (I got this from the example provided in your Tableau workbook),
(2) add the MONTH(Date) Dimension to the Marks shelf,
(3) edit the table calculation for each Measure to Compute Using: Advanced... Month of Date.
The table result is formatted with a lot of extra white space, because since the MONTH(Date) is on the Marks shelf, it seems to reserve space for every month. I know I can manually adjust the columns and rows to fit the data, but I prefer to let it auto-adjust so it will change as the data changes.
When I try to graph the results of these Average per Month calculations, I cannot get a normal graph because it always needs to include space for all the months.
I do these Average per Month calculations all the time on lots of data for our business. Is there an easier way? I'm surprised there isn't an automatic option for this type of calculation available in the right-click "Quick Table Calculation" list.