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

# help with calculation

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

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

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

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

• ###### 4. Re: help with calculation

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.