Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Shahzad_Ahsan
Contributor II

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

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.

6 Replies

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

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)




talk is cheap, supply exceeds demand
lironbaram
Honored Contributor II

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

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

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

Hi Gysbert

Thanks for your response.

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

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

By using the + operator like I showed above.


talk is cheap, supply exceeds demand
lironbaram
Honored Contributor II

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

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

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

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


talk is cheap, supply exceeds demand
Community Browser