Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

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
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)




talk is cheap, supply exceeds demand
lironbaram
Partner
Partner

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
Creator III
Creator III
Author

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.


talk is cheap, supply exceeds demand
lironbaram
Partner
Partner

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'}> }


talk is cheap, supply exceeds demand