topic Re: Set analysis IF Statement syntax in QlikView

Set analysis IF Statement syntax

Wed, 18 Sep 2013 08:07:35 GMT

hi. i have the following scenario

Itemnumber
ItemstatusToday = 1,2,6,7
Currency = USD, EUR
Purchase Price

The business rule states when Item Status is 6 or 7 show the Purchase Price in USD for the article number.

I have the following statement however it requires that an article number is selected to work otherwise it is blank.

this means i need to use set analysis, but i am unable to get the syntax right.

below is my code today.

sum(if(ItemstatusToday =6 or ItemstatusToday =7 and C = 'USD',POPrice)

and with the attempt of set analysis below;

sum(if(ItemstatusToday =6 or ItemstatusToday =7 and C = 'USD',)),

$<Itemnumber= {Itemnumber}>} POPrice

any help would be greatly appreciated.

Best

Bradley

Re: Set analysis IF Statement syntax

Gysbert_Wassenaar — Wed, 18 Sep 2013 08:32:04 GMT

Try sum({<Itemnumber={'$(=only(Itemnumber))'},ItemstatusToday={6,7},C={'USD'} >}POPrice)

Re: Set analysis IF Statement syntax

Wed, 18 Sep 2013 08:43:35 GMT

Hi!

unfortunately this did not work resulted in 0 result in the column POPrice

however when i used the following Set analysis Statement it worked.

is there any difference?

sum({<Itemnumber=, ItemstatusToday ={6,7}, C={'USD'} >} POPrice)

Re: Set analysis IF Statement syntax

er_mohit — Wed, 18 Sep 2013 08:47:00 GMT

Try this

sum({$<ItemstatusToday ={'6','7'}, C={'USD'} >} POPrice)

Re: Set analysis IF Statement syntax

Wed, 18 Sep 2013 08:52:32 GMT

try this

sum({<Itemnumber={'$(=sum(Distinct Itemnumber))'},ItemstatusToday={6,7},C={'USD'} >}POPrice)

****************************************************************************************

or try this

Try sum({<Itemnumber={'$(=only(Itemnumber))'},ItemstatusToday={6,7},C={'USD'} >}POPrice)

**************************************************************************************

or try this

sum({<Itemnumber=, ItemstatusToday ={6,7}, C={'USD'} >} POPrice)

Re: Set analysis IF Statement syntax

Gysbert_Wassenaar — Wed, 18 Sep 2013 08:56:36 GMT

Yes, there's a difference. Itemnumber={'$(=only(Itemnumber))'} means that one and only one Itemnumber must be selected or the expression won't return a result. Itemnumber= means that any selection in the field Itemnumber will be ignored and all item numbers will be used for the calculation.

Re: Set analysis IF Statement syntax

Wed, 18 Sep 2013 08:58:34 GMT

Thank you for your explanation.