6 Replies Latest reply on Feb 22, 2013 1:49 PM by Jim Thompson

# How does one roll-up a calculation back to a Summary level- and then USE it!??

So, I'm baffled. I have multiple line items of sales for multiple customers like this:

 CustomerID PurchaseDate ServiceType Amount 27836 12/1/2012 4 \$          643.90 27836 12/1/2012 2 \$          542.29 27836 12/1/2012 4 \$          221.85 27836 12/1/2012 1 \$          452.37 27836 12/1/2012 2 \$          474.18 27836 12/1/2012 1 \$               3.03 27836 12/1/2012 1 \$            33.37 27836 12/1/2012 3 \$          147.78 27836 12/1/2012 2 \$          240.84 21514 12/1/2012 3 \$          570.04 21514 12/1/2012 4 \$          408.15 21514 12/1/2012 3 \$          205.10 21514 12/1/2012 1 \$            82.58 21514 12/1/2012 3 \$          622.39 21514 12/1/2012 2 \$          618.81 and on and on and on and on and on and on and on and on

...as you can see the same customer (ID) has multiple lines, and, each line has a ServiceType associated with the purchase- and this is where the wheels come off... at least for me...

But I'm looking for the countD of customers of each of the 4 types.... I need to aggregate at the Customer level the customer's Type (ie is he type 1, type 2, etc) BY THE MAJORITY OF DOLLARS SOLD. Or, in other words, if he bought one type 1 for \$500 and 3 type 3 for \$30 each, he is a type 1 customer.... (ie it rolls up to the summary customer level).

The GOAL of this exercise is to show in a pie chart the breakdown of COUNT of Customer Types with the count and then sum of the sales revenue...

I'm out of ideas, since, the moment I aggregate (in a calculated field of course) the contributions and then determine the largest and return a type, I cannot use this calculated field as a Dimension....sigh... I can use it as a measure, but you can't filter on it, nor, use it as an angle on the pie chart... Aarrgh!

In short, I think I'm trying to calculate on a lower layer and have the result roll up to the summary (customer) layer but I cannot figure out how to go about doing this... Does anyone have a suggestion?? thanks! (sorry about the long winded explanation)....

• ###### 1. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

Hi Jim, I don't have the time to really understand what your issue is right now (sorry) but now that it is 20+ days old I fear it might get lost, so I'm bumping it.

--Shawn

• ###### 2. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

Hi Jim,

See the attached.

When you want to aggregate at one level and then re-aggregate at another, that generally calls for either hacking some SQL or table calculations. The advantage to table calculations is that we can do them entirely in Tableau. I did the following:

1. Set up the WC Customer ID table calc. This does a WINDOW_COUNT(COUNTD([CustomerID])), with some optimizations to ensure that only one row is returned (to avoid duplicates) and speed up performance in v7.

2. Brought that into the view and set the Compute Using to Customer.

3. On the __ContribType pill, unchecked "Ignore in Table Calculations." This lets Tableau partition (restart) the calc on each value of ContribType.

4. Once I had that working in a crosstab, I duplicated the worksheet, set the Mark Type to Pie, and put the pills where you see them. I also Ctrl+dragged a copy of the WC Customer ID pill onto the Filtes Shelf and set the Filter to non-Null values. This reduces the number of marks Tableau is attempting to draw and can speed views with a lot of marks.

Jonathan

• ###### 3. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

You Sir, are a wizard! Thank you. This little snippet of Window_Count code, but along the specified dimension (CustomerID), is exactly what I needed! Light bulb!

thanks!

jim!

• ###### 4. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

You're welcome!

• ###### 5. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

No Jim he's a Zen Master! Wizards are on other forums.

--Shawn

• ###### 6. Re: How does one roll-up a calculation back to a Summary level- and then USE it!??

Doh!