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

    Aggregating aggregated fields - Hirschmann Herfindahl

    Keith Duncan

      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