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
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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

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

Hi

try using single quote

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

Contributor II
Contributor II

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

Thank you

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

Specialist II
Specialist II

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

Partner
Partner

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

YTD, MTD issue

Hope this helps

Contributor II
Contributor II

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

Contributor II
Contributor II

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

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.