Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set analysis IF Statement syntax

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

Tags (2)
1 Solution

Accepted Solutions
er_mohit
Honored Contributor II

Re: Set analysis IF Statement syntax

Try this

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

6 Replies

Re: Set analysis IF Statement syntax

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


talk is cheap, supply exceeds demand
Not applicable

Re: Set analysis IF Statement syntax

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)

er_mohit
Honored Contributor II

Re: Set analysis IF Statement syntax

Try this

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

Highlighted
Not applicable

Re: Set analysis IF Statement syntax

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Set analysis IF Statement syntax

Thank you for your explanation.

Community Browser