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

If, then and else formula with a sum in it

Anonymous — Tue, 29 Sep 2015 13:38:52 GMT

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?

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

JonnyPoole — Thu, 01 Oct 2015 01:19:14 GMT

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

Anonymous — Thu, 01 Oct 2015 07:49:54 GMT

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

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

JonnyPoole — Thu, 01 Oct 2015 13:11:16 GMT

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

Thu, 01 Oct 2015 16:29:52 GMT

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

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

Anonymous — Thu, 08 Oct 2015 07:07:24 GMT

Thanks, it's working now!

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

Anonymous — Thu, 08 Oct 2015 07:08:54 GMT

Thanks for your reaction.

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

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

Anonymous — Thu, 08 Oct 2015 07:37:21 GMT

Hi Dag,

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