7 Replies Latest reply: Apr 26, 2017 2:05 PM by Mahender Pannala

# 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

• ###### Re: Set Analysis using Date Less than or Equal to

Hi

try using single quote

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

• ###### Re: Set Analysis using Date Less than or Equal to

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

Thank you

• ###### Re: Set Analysis using Date Less than or Equal to

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

• ###### Re: Set Analysis using Date Less than or Equal to

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

• ###### Re: Set Analysis using Date Less than or Equal to

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)

• ###### Re: Set Analysis using Date Less than or Equal to

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

YTD, MTD issue

Hope this helps

• ###### Re: Set Analysis using Date Less than or Equal to

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