topic Re: Expression Calculation with IF statement in QlikView

Expression Calculation with IF statement

Mon, 30 Mar 2015 10:12:47 GMT

Hey Everyone.

I am struggling with a statement, if anybody can help it would be much appreciated.
The situation is as followed.

I have several columns as shown below:

Column 4 has the following condition:

if(above(sum(Result)) - sum(MoneyCount) > 500 and

above(sum(Result))>1000,rgb(255,0,0))

The point of this is:

1, When MachineSaldo (Result) is above 1000 an action has to be taken in the form of MoneyCount.

2, if the result after the MoneyCount is above 500 show as red because that is our limit, it cannot exceed 500.

Now the above condition does exactly that, as seen in row 2.

however we now want an extra condition, namely:

3, IF there has been a MoneyCount(even if MachineSaldo is below 1000) and the result still exceeds 500 it has to be red as well.

So in the above example row 3 (27-3-2015) the Result after MoneyCount column has to become red as in row 2. but row 1 can stay as it is right now because no MoneyCount action has been taken.

I thought it was as easy as putting >0 after MoneyCount in the condition, but that does not seem to work.

Kind Regards.

Mike Cleven

Re: Expression Calculation with IF statement

yura_ratu — Tue, 31 Mar 2015 13:08:13 GMT

Hi. Try this

if(above(sum(Result)) - sum(MoneyCount) > 500 and

(above(sum(Result))>1000 or sum(MoneyCount)>0),rgb(255,0,0))

(deleted duplicated or)

Re: Expression Calculation with IF statement

Tue, 31 Mar 2015 13:41:47 GMT

Unfortunately that does not work.
After I try this the cells wich fall in range suddenly have no values in them and appear empty.

Re: Expression Calculation with IF statement

ramoncova06 — Tue, 31 Mar 2015 14:20:32 GMT

there is a duplicate "or: on Yuriy syntax, did you removed it

if(above(sum(Result)) - sum(MoneyCount) > 500 and

(above(sum(Result))>1000 or sum(MoneyCount)>0),rgb(255,0,0))

Re: Expression Calculation with IF statement

Tue, 31 Mar 2015 14:31:40 GMT

Wow...

Okay now i feel stupid haha.

Thanks guys! it works perfect now.