7 Replies Latest reply: Oct 8, 2015 3:07 AM by Louise Grauss-Spoormaker

If, then and else formula with a sum in it

How do I write the right formula for:

If [Field B] =0

then

sum([Field C]*[Field D])

If [Field B] >=1

then

sum([Field B]*[Field C]*[Field D])

I want to make an Master Item of it for a KPI diagram and I want the total sum in it.

In the example that would be € 4.312,60

Field AField BField CField DSum
81229,952.875,20
02039,95799,00
4819,95638,40
Sum4.312,60

I want it to be looked at per line so I think that the 'sum' comes first and then the 'if' but I can not get a working formula.

I tried it both ways

sum(if( [Field B] = 0, Then [Field C]*[Field D], Else[Field B]*[Field C]*[Field D]))

and

if [Field B] = 0,

Then sum([Field C]*[Field D]),

Else sum([Field B]*[Field C]*[Field D]))

End if

and many versions of it, with and without commas or with or without spaces or with or without '= sign', or with and without enters, with capital letters, etc.

Is there anyone who can help me?

• Re: If, then and else formula with a sum in it

Try this:

sum

If [Field B] =0,  [Field C]*[Field D] ,

If  ( [Field B] >=1 , [Field B]*[Field C]*[Field D])

)

)

• Re: If, then and else formula with a sum in it

Do you write it on different lines or on 1 line?

And where do you have the spaces?

I now have it like this:

sum(If[Field B] =0, [Field C]*[Field D], If( [Field B] >=1, [Field B]*[Field C]*[Field D]))

• Re: If, then and else formula with a sum in it

So sorry,  I forgot an opening parenthesis after the first if

Sum ( if (

Spaces and New lines are generally ok.

• Re: If, then and else formula with a sum in it

Louise,

It would be best for performance if you were to create a column in the data set that had what you needed.  Meaning, during the load script there would be a new column.

if( [Field B] = 0, [Field C]*[Field D], [Field B]*[Field C]*[Field D]) as [My KPI]

FROM ...;

Then your chart/KPI object becomes SUM([My KPI])