Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

lou_grauss
New Contributor II

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
Employee
Employee

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.

7 Replies
Employee
Employee

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

     )

)

lou_grauss
New Contributor II

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

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

Employee
Employee

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.

Not applicable

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.

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

lou_grauss
New Contributor II

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

Thanks, it's working now!

lou_grauss
New Contributor II

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

Thanks for your reaction.

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

lou_grauss
New Contributor II

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

Hi Dag,

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

Community Browser