Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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?

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

Sum ( if (

Spaces and New lines are generally ok.

View solution in original post

7 Replies
JonnyPoole
Employee
Employee

Try this:

sum

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

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

     )

)

Anonymous
Not applicable
Author

Too bad, it's not working.

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]))

JonnyPoole
Employee
Employee

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

Sum ( if (

Spaces and New lines are generally ok.

Not applicable
Author

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.

LOAD *,

  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])

Anonymous
Not applicable
Author

Thanks, it's working now!

Anonymous
Not applicable
Author

Thanks for your reaction.

I'm gonna keep it in mind and try it the next time.

Anonymous
Not applicable
Author

Hi Dag,

I tried it in another app and it's working perfectly!