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?