Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
hi
Count(distinct if((REQ_CREATION_DATE>=Today()-7 or PO_RECEIVE_DATE >=Today()-7) and match(REQ_STATUS,'APPROVED' ,'CANCELLED ')=0, REQ_NUMBER))
Hi Gysbert
Thanks for your response.
I mostly use this set analysis. But how we can manage 'OR' here.
By using the + operator like I showed above.
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
You can always intersect your set modifier with the user selections using the * operator: {<MyField>*= {'MySelection'}> }