How to use multiple values in if else statement with same dimension in qlik sense

[Shahzad_Ahsan]

Hi Everyone

This is SQL statement

Count(DISTINCT REQ_NUMBER) Where (REQ_CREATION_DATE >=sysdate-7 or PO_RECEIVE_DATE >=sysdate-7)

and REQ_STATUS  NOT IN ('APPROVED','CANCELLED')

I want to implement this as an expression. I have done something like this

Count(distinct if((REQ_CREATION_DATE>=Today()-7 or PO_RECEIVE_DATE >=Today()-7)  and REQ_STATUS<>'APPROVED' , REQ_NUMBER))

How to give multiple values Like APPROVED and CANCELLED at the same time.

[Gysbert_Wassenaar]

and NOT Match(REQ_STATUS,'APPROVED' , 'CANCELLED')

But a set analysis expression like this usually performs better than a count( if(...) )

count( { <REQ_CREATION_DATE={">=$(=Today()-7)"}>

              + <PO_RECEIVE_DATE ={">=$(=Today()-7)"}>

              - <REQ_STATUS={'APPROVED' , CANCELLED'}> } DISTINCT REQ_NUMBER)

[lironbaram]

hi

Count(distinct if((REQ_CREATION_DATE>=Today()-7 or PO_RECEIVE_DATE >=Today()-7)  and match(REQ_STATUS,'APPROVED' ,'CANCELLED ')=0, REQ_NUMBER))

[Shahzad_Ahsan]

Hi Gysbert

Thanks for your response.

I mostly use this set analysis. But how we can manage 'OR' here.

[Gysbert_Wassenaar]

By using the + operator like I showed above.

[lironbaram]

hi

just a little comment from my side ,

if performance allows me , i'll always prefer to use if statement

because it is effected fully by user selections

while set analysis is disregarding user selections in the fields you use in the expression

because it's done in the background the user might not know what is the number he sees

[Gysbert_Wassenaar]

You can always intersect your set modifier with the user selections using the * operator: {<MyField>*= {'MySelection'}> }