Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
er_mohit
Master II
Master II

Try this

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

View solution in original post

6 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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
Master II
Master II

Try this

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

Not applicable
Author

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)

Gysbert_Wassenaar

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
Author

Thank you for your explanation.