2 Replies Latest reply on May 7, 2014 1:45 PM by Lynn Snow

Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

Lynn Snow Feb 19, 2013 12:44 PM

I am trying to figure out if Tableau can aggregate data at higher levels, that can be used in other analyses based on those aggregated values. In my mind, this is similar to using a SumIf formula in Excel. I've search for a solution, but haven't found anything that explains how to do this in Tableau (yet).

I am guessing the only answer is to calculate these tables of summarized data outside of Tableau (such as in SQL). However, Tableau is proving to be such a powerful tool, that it would make sense for it to have this functionality built-in. The simplest route would be to load in the data at the most granular level of detail, then use Tableau to do ALL the aggregation.

Below are two example situations of what I would like to accomplish. I've attached an Excel file with dummy data. The example input data provides details for individual tickets of lbs purchased (I work in a recycling business). Each ticket may have multiple products on it. I want to load this into Tableau, because it is the lowest level of detail.

(1) Calculate % of Total based on an ID field

For each Product on each Ticket, I want to calculate the % of Total based on Lbs. Therefore I need to Sum the Lbs for every unique Ticket # first, then I can divide the individual Product Lbs by the Total Lbs per Ticket.

(2) Create an aggregated summary table, then calculate averages based on the summarized values

Using the original data, I would like to see an average of the Total Lbs per Month by Quarter. However, if my data is at the detailed Ticket level, I can only do averages per Ticket line item at the quarter level. The data first needs to be aggregated by Month (sum lbs by month) then I want to calculate averages based on these monthly totals.

I have many scenarios similar to these two that need a "SumIf" type calculation.

Example Data for Tableau SumIf Questions.xlsx 21.2 KB

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 (in response to Lynn Snow)
Hi 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

sumif question.twbx.zip 36.9 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

Lynn Snow May 7, 2014 1:45 PM (in response to Jonathan Drummey)
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.

Average Calculation Question.twbx 64.2 KB

Example Data for Tableau Average Calculation Question.xlsx 87.1 KB
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

1. Re: Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

2. Re: Can I aggregate data at a higher level, then calculated summaries based on those aggregated values?

Actions

More Like This