Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 A | Field B | Field C | Field D | Sum |
---|---|---|---|---|
8 | 12 | 29,95 | 2.875,20 | |
0 | 20 | 39,95 | 799,00 | |
4 | 8 | 19,95 | 638,40 | |
Sum | 4.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?
So sorry, I forgot an opening parenthesis after the first if
Sum ( if (
Spaces and New lines are generally ok.
Try this:
sum
(
If [Field B] =0, [Field C]*[Field D] ,
If ( [Field B] >=1 , [Field B]*[Field C]*[Field D])
)
)
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]))
So sorry, I forgot an opening parenthesis after the first if
Sum ( if (
Spaces and New lines are generally ok.
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])
Thanks, it's working now!
Thanks for your reaction.
I'm gonna keep it in mind and try it the next time.
Hi Dag,
I tried it in another app and it's working perfectly!