4 Replies Latest reply on Sep 22, 2013 11:29 PM by Keith Duncan

# Aggregating aggregated fields - Hirschmann Herfindahl

Hi all,

I'm trying to calculate the Hirschmann Herfindahl index for a group of producers. HH index basically calculates the market share and then sums the square of the market shares to produce an index. It thus requires two calculations:

Market Share = SUM([Value]) / TOTAL(SUM([Value])

HHI = SUM([Market Share]^2)

You can see where this is going... I get the old "Cannot aggregate an aggregated field" complaint.

I created the attached workbook:

Sheet 1 is how far I've got it working - I specify for the HH value to be calculated by Supplier and it's all nice. However, the Grand Total shows a value of 1 which is clearly incorrect. But look down to the very bottom of Tableau and it says

SUM of AGG(Hirschmann Herfindahl): 0.5086

which is of course the correct value I'm trying to get to.

Sheet 2: When I try to create what I'm after (a sum of the HH), I get 1 again, presumably because I have not been able to sum by Supplier.

How is it that Tableau allows itself to aggregate a Summed field, but doesn't allow me to do it?

Thanks,

Keith

• ###### 1. Re: Aggregating aggregated fields - Hirschmann Herfindahl

Hi Keith, please can you save your data as a packaged workbook (.twbx extension) rather than a workbook (.twb extension). Otherwise the rest of us wouldn't be able to see your data.

• ###### 2. Re: Aggregating aggregated fields - Hirschmann Herfindahl

The number at the bottom will probably be either total() or window_sum() of SUM([Market Share]^2). You should be able to wrap your on one of those as well

• ###### 3. Re: Re: Aggregating aggregated fields - Hirschmann Herfindahl

Sorry.

Please find attached.

Thanks for your interest,

Keith

• ###### 4. Re: Re: Aggregating aggregated fields - Hirschmann Herfindahl

Thanks Alex,

WINDOW_SUM did the job. To calculate this number at the supplier level, I had to add Supplier as a row and select Compute using -> Supplier (Sheet 1)

However, this of course gives the same answer for each supplier. To get one single number (i.e. the Hirschman Herfindahl index), I moved the Supplier dimension to the Pages shelf (Sheet 2).

Regards,

Keith