4 Replies Latest reply on Sep 21, 2018 2:39 PM by Michael Gillespie

    help with calculation

    Elvira Giampapa

      Looking for help with the following:

       

      Account #          Segment              Opportunities

      1001                     2                               15

      1002                     1                               72

      1003                     2                                6  

      1004                     1                              104

       

       

      I need to add Partner and want segment and want it to look like this:

      Where the segment calculation is based on all the opportunities within the Account # regardless of Partner

       

       

      Account #        Segment          Partner               Opportunities                    

      1001                   2                        A                        2

                                                           B                        3

                                                           C                        4

                                                           D                        6

      1002                  1                         A                       50

                                                           B                       10

                                                           C                       10

                                                           D                         2          

      1003                  3                         A                         6

      1004                  1                         C                      104

       

       

      Here is the calculation that I am using for Segment:

       

       

      If
      [Opportunities] = 18 then '1'

      ELSEIF
      [Opportunities] > 18 then '1'

      ELSEIF
      [Opportunities] < 18 then '2'

      ELSEIF
      [Opportunities] > 10 then '2'

      ELSEIF
      [Opportunities] = 10 then '2'

      ELSEIF
      [Opportunities] < 10 then '2'

      ELSEIF
      [Opportunities] = 5 THEN '3'

      ELSEIF
      [Opportunities] > 5 THEN '3'

      ELSEif
      [Opportunities] < 5 then '4'

      Else
      'NULL'

      End

       

       

       

        • 1. Re: help with calculation
          Ken Flerlage

          I'd suggest creating an LOD calculation that will sum up the opportunities by Account. It would look something like this: {FIXED [Account]: SUM([Opportunities])}

           

          Then use that calculated field in your Segment calculated field instead of Opportunities.

          • 2. Re: help with calculation
            Michael Gillespie

            Elvira, let's break down what you want in plain language.

             

            For each Account #, calculate the total number of Opportunities

            Based on that number assign a Segment value to the Account #

             

            Correct?

             

            If so, try this.  We'll use a Level of Detail (LOD) calculation to force Tableau to sum up the Opportunities at the Account # level.

             

            IF {FIXED [Account #]: SUM([Opportunities])} >= 18

            THEN '1'

            ELSEIF {FIXED [Account #]: SUM([Opportunities])} < 18

            AND {FIXED [Account #]: SUM([Opportunities])} >= 10
            THEN '2'

            ELSEIF {FIXED [Account #]: SUM([Opportunities])} < 10

            AND {FIXED [Account #]: SUM([Opportunities])} >= 5
            THEN '3'

            ELSEIF {FIXED [Account #]: SUM([Opportunities])} < 5
            THEN '4'

            ELSE '5'

            END

             

            Note that you can combine the > and = in one statement, and you can combine the >= test and the < test into one IF statement.

             

            Try that and see what happens.

            • 3. Re: help with calculation
              Elvira Giampapa

              Thanks for your help Michael.  I changed the calculation a little but it worked!

              • 4. Re: help with calculation
                Michael Gillespie

                Magnifica! (I hope I've guessed your native language correctly!)

                 

                As Ken illustrated, you can simplify the calculation by taking the LOD out and making it a standalone calc, then referencing it in the main calculation.

                 

                Glad I could help.

                 

                See you in New Orleans! #tc18