Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis using Date Less than or Equal to

I have a requirement to count the transactions that are Less than or equal to a Date. I wrote an expression using IF Statement but that is performing bad. Is there a better way to write in set analysis.

Below is the IF Statement which gives me right answer:

SUM(IF([Transaction Term Date] <= [R12M PERIOD] AND [Movement Type] = 'Staffing Hires', Transactions)

Below is the set Analysis expression which is not working

SUM({<[Transaction Term Date] = {"<= [R12M PERIOD]"},[Movement Type] = {'Staffing Hires'} >}Transactions).

Appreciate the help!!

Thank You

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Yes Vijay, realized that my both dates were not in the same format. corrected it and below is my expression and it is working now.

SUM({<[Transaction Term Date]={"<=$(=DATE([R12M PERIOD]))"},[Movement Type]={'Staffing Hires'} >}Transactions)

View solution in original post

8 Replies
vvira1316
Specialist II
Specialist II

Hi

try using single quote

SUM({<[Transaction Term Date] = {'<= [R12M PERIOD]'},[Movement Type] = {'Staffing Hires'} >}Transactions).

Anonymous
Not applicable
Author

Thanks for the reply. Single quote didn't work.

Thank you

vvira1316
Specialist II
Specialist II

Hi,

you are comparing it with field so not sure what may be causing it to not work.... it can be date formats of both fields (are they same while comparing), are you getting only one value for  [R12M PERIOD] (if not then it can be an issue), etc.

may be you want to get field ( [R12M PERIOD]) value in a variable and try

In my case following set analysis provides results and as you can see I've single quote and >= for greater than comparison of date

=Count( {<

  [Para Review Date Y/N]={'No'},

  [Agreement Date]={'>=$(=vAgreementDate)'},

  [Document Status]={'EDS*', 'EDC*'},

  [Main Document Type]={'VAL1', 'VAL2'}> *

  (

   <[Is Sub Document?]={0}> +

   <[Is Sub Document?]={1},

   [Sub Document Type]={'AMENDMENT', 'SEPARATION AND AMENDMENT'}>

  )

  } [Counterparty Document: Document ID])

vvira1316
Specialist II
Specialist II

vAgreementDate is defined as =Date#('01/01/2014', 'MM/DD/YYYY')

OmarBenSalem

Please refer to this thread; I tried to explain in steps how to handle time expressions:

YTD, MTD issue

Hope this helps

Anonymous
Not applicable
Author

Thanks Omar, that really helped in under standing the time expressions

Anonymous
Not applicable
Author

Yes Vijay, realized that my both dates were not in the same format. corrected it and below is my expression and it is working now.

SUM({<[Transaction Term Date]={"<=$(=DATE([R12M PERIOD]))"},[Movement Type]={'Staffing Hires'} >}Transactions)

jkadowGkn
Contributor
Contributor

I think it is important to note that an extra equal sign (=) is used when adding the Date function. See the underlined equal sign in the function...

SUM({<[Transaction Term Date]={"<=$(=DATE([R12M PERIOD]))"},[Movement Type]={'Staffing Hires'} >}Transactions)

I was stuck on that for a couple of hours, and figured pointing it out may help others.