topic Re: Diff between the 2 expressions in QlikView

Diff between the 2 expressions

surajap123 — Tue, 06 May 2014 10:49:16 GMT

Hi All,

I thought both the below statements are same, so i created the 2nd one using set analysis. But i am getting different outputs

(Sum(IF(Status = 'Cancelled', (Amount)))) + (Sum(IF(Status = 'Booked', (Amount))))

sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

Could you please tell me why they are giving different results?

Re: Diff between the 2 expressions

its_anandrjs — Tue, 06 May 2014 10:54:11 GMT

You can try like

Sum( {<Status={'Cancelled','Booked'}>}Amount)

And may be your if statement which is this

(Sum(IF(Status = 'Cancelled', (Amount)))) + (Sum(IF(Status = 'Booked', (Amount))))

gives wrong result try to check and cross check this by using

=Sum(IF(Match(Status,'Cancelled','Booked'), Amount))

Re: Diff between the 2 expressions

sudeepkm — Tue, 06 May 2014 10:57:31 GMT

Please try like below

sum( {<Status={'Cancelled', 'Booked'}>}Amount)

Re: Diff between the 2 expressions

Anonymous — Tue, 06 May 2014 10:58:43 GMT

hi,

try like this

for 2nd expression

sum( {<Status={'Cancelled','Booked'}>}Amount)

Re: Diff between the 2 expressions

sudeepkm — Tue, 06 May 2014 11:03:54 GMT

it looks like when you use this expression

sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

It considers the Status as 'Booked' only

and

when you use like

sum( {<Status={'Cancelled', 'Booked'}>}Amount) then it considers either Cancelled or Booked.

Re: Diff between the 2 expressions

tresB — Tue, 06 May 2014 11:05:11 GMT

Set analysis would not respond to your Status field selection here, while the other expression without set analysis does. Apart from that:

(Sum(IF(Status = 'Cancelled', (Amount)))) + (Sum(IF(Status = 'Booked', (Amount))))

This executes like Status='Cancelled' OR 'Booked'

sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

This executes like Status='Cancelled' AND 'Booked' // which is not really meaningful

Update: And when it is same field, it take the later one(here 'Booked')

Re: Diff between the 2 expressions

sudeepkm — Tue, 06 May 2014 11:15:10 GMT

Hi Tresesco,

I just created a sample app where I used the expression like below.

sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

I didn't have data points that has both Cancelled and Booked Status that means it (the AND operator) should have given me 0 as there were no items in common, however I found that the result is the value of Status = "Booked". Can you please check once and confirm? I tested this using a sample app of mine.

Re: Diff between the 2 expressions

surajap123 — Tue, 06 May 2014 11:15:27 GMT

Hi Tresesco,

As you mentioned, Could you please explain me why Set analysis would not respond to your Status field selection in the below expression-

sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

Re: Diff between the 2 expressions

SunilChauhan — Tue, 06 May 2014 11:16:27 GMT

both are same .may be not distinct and distinct results

but you can write like Sandeep and Nisha.

hope this helps

Re: Diff between the 2 expressions

sudeepkm — Tue, 06 May 2014 11:16:52 GMT

I just checked your update. Thanks a lot.

In case of same field it takes the later one. Thanks again.

Re: Diff between the 2 expressions

tresB — Tue, 06 May 2014 11:20:10 GMT

Suraj,

Set analysis is meant for that. In the expression: sum( {<Status={'Cancelled'},Status={'Booked'}>}Amount)

you specifically say the values to be considered for field Status. Hence, it ignores the selection for that field.

Re: Diff between the 2 expressions

SunilChauhan — Tue, 06 May 2014 11:24:50 GMT

first one is showing correct result

and second one is overriding the status to Booked.

changed the order of booked and cancelled in second expression.

it will give other result.

i mean to say it taking last one status and showing value on that.

hope this helps

Re: Diff between the 2 expressions

sudeepkm — Tue, 06 May 2014 11:28:59 GMT

Given below is a screenshot of what I tested in my sample Application.

As you can see when I have the expression {<Field={'value1', 'value2'} >} it considers as either or that means all from both the selection.

when I gave the expression like {<Field={'value1'},Field={'value2'} >} it considers it as two selections of same field but one after another. and finally it considers the second selection. this is same response when we select a value from a list box and then we select another value from the same list box. the first selection is overridden by the second selection.

Re: Diff between the 2 expressions

surajap123 — Tue, 06 May 2014 11:34:58 GMT

Thanks a lot to everyone for explaining it so well, especially tresesco and sudeep

Re: Diff between the 2 expressions

preminqlik — Tue, 06 May 2014 11:37:26 GMT

you can use the following expressions :

Sum(IF(Status = 'Cancelled' or Status = 'Booked', Amount)

sum( {<Status={'Cancelled' , 'Booked'}>}Amount)

Re: Diff between the 2 expressions

Joseph_Musekura — Tue, 06 May 2014 11:47:02 GMT

Hi,

I agree with Sudeep,

sum( {<Status={'Cancelled','Booked'}>}Amount), this will returns both selections

If you add 1

sum( {1<Status={'Cancelled', 'Booked'}>}Amount), it will disregard any selection made

Regards,