6 Replies Latest reply on Sep 12, 2012 1:27 AM by steve.parsons

    Sum of squares

      Hi, I've just started using tableau, so apologies if my question is obvious.

       

      I'm trying to display the sum of the squares of market shares (hefendahl index).

       

      i.e. if a row has 3 values:

      A  B  C  TOTAL

      1  4   5   10

      The value I want to calculate is:

      (1/10)^2 + (4/10)^2 + (5/10)^2 = 0.42

       

      Columns A, B and C could be totals from other dimensions.

       

      Sample data:

      Company, A, B, C

      OneCo, 1, 4, 5

      TwoCo, 5,0,3

      ThreeCo, 0,0,4

       

      I'd like to display:

      OneCo     0.42

      TwoCo     0.53125

      ThreeCo   1

       

      Any help would be greatly appreciated.

        • 1. Re: Sum of squares
          Robin Kennedy

          This should be fairly easy to accomplish by creating a calculated value (right click in Measures pane and select Create Calculated Field). Give your field a name, like 'Herfindahl Index' and then in the formula pane write

           

          ([A]/[TOTAL])^2+([B]/[TOTAL])^2+([C]/[TOTAL])^2

           

          If you don't have a TOTAL measure, just replace it with ([A]+[B]+[C])

           

          Does that do the trick?

          • 2. Re: Sum of squares

            Hi Robin,

             

            Thanks for your answer. However, I just realised that I posted my input data incorectly.

             

            Your solution would have worked fine given the data set I gave, however, what I actually have is:

             

            COMPANY, CATEGORY, VAL

            OneCo, A, 1

            OneCo, B, 5

            OneCo, C, 4

            TwoCo, A, 5

            TwoCo, B, 0

            TwoCo, C, 3

            ThreeCo, A, 0

            ThreeCo, B, 0

            ThreeCo, C, 4

             

            I've been working with customised grand total calculations at the moment, as my calculation goes across columns.

            • 3. Re: Sum of squares
              sirajmandayippurath

              Steve,

               

              Have you tried using the "WINDOW_" functions to address the sum of fields at the pane or table level?

               

              I have created a sample with the data that you provide. Is this what you were looking for?

               

              Cheers

              Siraj

              • 4. Re: Sum of squares

                Hi Siraj,

                 

                Thanks for the help. Your example gives me the indivual ratios for each A,B,C, but not the sum of their squares.

                 

                The output I want is one row per company as follows:

                 

                Company      CalculatedValue

                OneCo          0.42

                TwoCo          0.5061728

                ThreeCo       1

                 

                Thanks for the tip about the window_ functions. No joy so far, but I'll keep trying things out

                • 5. Re: Sum of squares
                  sirajmandayippurath

                  Hi Steve,

                   

                  I have added a second table calculation that allows you to get to that result.

                   

                  You will also notice that in order to ensure that the Final Result is displayed on the final row, I have used the SIZE() and the INDEX() functions.

                   

                  Hope this helps..

                   

                  Cheers

                  Siraj

                  • 6. Re: Sum of squares

                    Very helpful, thanks.

                     

                    Ideally, I don't want to show the extra columns and rows. Do I just have to 'hide' them manually, or is it possible to remove them properly ?

                    If I take category off the rows, and put it in the level of detail box, I get overlapping text. I've not found a way to get the correct results using things like  'IF FIRST()=0' or IF(INDEX()=SIZE()'.