Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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)

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
Highlighted
Not applicable

Re: Set analysis IF Statement syntax

Thank you for your explanation.

Community Browser