1 2 Previous Next 26 Replies Latest reply on Feb 27, 2015 1:07 PM by Jonathan Drummey

Aggregating over an aggregate

I'm sure this has been asked countless times before but despite extensive google searching I haven't found a sufficient solution to my problem hence asking (sorry!) - I'm very new to Tableau.

I have a data set that resembles the following

SupplierSpend
Supplier11
Supplier22
Supplier34
Supplier47
Supplier510
Supplier611
Supplier714
Supplier85
Supplier911
Supplier106

I want to do the following;

1. Calculate the highest number in the set (in this case 14)
2. Work out which quantile each number belongs in (Q1 = 1 - 14/4, Q2 = 14/4 - 14/2, Q3 = 14/2 - 14*(3/4), Q4 = 14*(3/4) - 14)
3. Aggregate over step 2, to show the frequency distriubution of quantiles

I used a calculated column with IF .. ELSEIF .. ELSEIF... ELSE.. END to determine part 2 above, but when I try to COUNT to achieve the 3rd part, I'm told I can't aggregate over an aggregate. Is there another much cleverer way to achieve what I want?

• 1. Re: Aggregating over an aggregate

Use WINDOW_COUNT to count aggregates.

Other aggs to agg aggregates:

WINDOW_SUM.

WINDOW_MIN

WINDOW_AVG

etc.

1 of 1 people found this helpful
• 2. Re: Aggregating over an aggregate

I tried using WINDOW_COUNT on the calculated column I used in step 2 above but it returns a flat value for every entry in the column, which is equivalent to a total count of the quantile to which each value belongs. So (in English) if there were four rows, the first two rows had Spend such that they belonged in Quantile 1, the third row had Spend such that it was assigned to Quantile 2, and the third row had Spend such that it belonged to Quantile 3, the WINDOW_COUNT returns 1 + 1 + 2 + 3 = 6 as the value for all 4 rows.

What I want to achieve is that each quantile has a COUNT of the number of rows that fit into that quantile, so really in this reduced example I want three rows, one column with the quantiles, one with the vlaues 2, 1, 1. Does that make sense?

• 3. Re: Aggregating over an aggregate

Yup.

WINDOW_(whatever) makes a table calc.  You can specify how a table calc gets partitioned (by default tableau does either TABLE(doen) or TABLE(across) ) and sometimes you have to tell ti to do something different.

At this point I can only guess what you'll need to do to tell it where to run, and where to restart.  I'll need an extracted workbook (TWBX) to play with.

• 4. Re: Aggregating over an aggregate

You might find answers here

http://vizdiff.blogspot.com/2015/02/histograms-on-aggregated-measures.html

On Wed, Feb 25, 2015 at 10:08 AM, Joe Oppelt <

1 of 1 people found this helpful
• 5. Re: Aggregating over an aggregate

I've created the extracted workbook (.tde file) but I can't see anywhere that I can attach it here?

• 6. Re: Aggregating over an aggregate

Either you can clinck rhe advanced editor where you can do attachments, or edit one of your replies (which puts you into the advanced editor.)

• 7. Re: Aggregating over an aggregate

on top right corner, use advanced editor.

On Wed, Feb 25, 2015 at 11:21 AM, Hamish Wilson <

• 8. Re: Aggregating over an aggregate

Done - attachment above. Is that just the raw data in the .tde file? Or does that give you access to any analysis I've performed on the data as well?

If it doesn't give you the analysis then just a brief explanation of what I've done.

1. Placed Supplier on Rows

2. Placed Spend on Columns

3. Toggled to show text rather than default graph (very annoying - can I set text as the default somehow?)

4. Clicked Analysis > Create Calculated Field and created a Field called maxSpend with the formula TOTAL(MAX([Spend])).

5. Added maxSpend to Rows and again toggled to show text rather than graph.

6. Clicked Analysis > Create Calculated Field and created a Field called [quartiles] with the formula

IF (SUM([Spend]) < maxSpend*(1/4)) THEN 1

ELSEIF (SUM([Spend]) < maxSpend*(2/4)) THEN 2

ELSEIF (SUM([Spend]) < maxSpend*(3/4)) THEN 3

ELSE 4

END

7. Added [quartiles] to Rows and again reverted to showing text rather than graph.

That's as far as I've got. Now what I want to do is essentially ignore the first three columns; Supplier, Spend and maxSpend, and count instances of [quartiles] as a frequency distribution; so 1 occurs 4 times, 2 occurs twice etc.

• 9. Re: Aggregating over an aggregate

Hamish -- In Tableau Desktop under FILE select "Extract Packaged Workbook".

That gives you a TWBX containing your workbook and data.  Upload that here.

• 10. Re: Aggregating over an aggregate

Done. Please see attached.

• 11. Re: Aggregating over an aggregate

OK, for that calc you wanted to use TABLE(down) on the [quartile] table calc.

• 12. Re: Aggregating over an aggregate

Lookinfg at this further, what's wrong with the voz you provided?  Aren't those quartile numbers correct?  the MAX is correct.  (WINDOW_MAX would have done that too.)  And the various sums are getting proper quartile numbers, aren't they?

If not, what are you expecting?

• 13. Re: Aggregating over an aggregate

Everything there is correct and CC's are functioning as intended, but there's another step I don't know how to realise - I now want to aggregate over the quartile column. So ultimately I want to end up with

QuartileFreq
14
22
32
42

but if I try to now COUNT(quartile) it complains that I'm aggregating over an aggregate column. You mentioned something earlier about window_summing with partitioning? That sounds similar to T-SQL window functions that would achieve what I'm looking for in that context

• 14. Re: Aggregating over an aggregate

Not as easy as I thought it would be.

I can get the quartiles to sort so all the 1s and 2s are together, but I can't get the next calc (window_count([quartile])) to restart for each quartile.  (See Calculation1).

I even thought I could get a calc to conditionally count.  (See calculation2).  But all that's doing is acting on the table in the order shows on sheet1.

Jonathan Drummey -- is there a way to get this to happen?

1 of 1 people found this helpful
1 2 Previous Next