Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi
try using single quote
SUM({<[Transaction Term Date] = {'<= [R12M PERIOD]'},[Movement Type] = {'Staffing Hires'} >}Transactions).
Thanks for the reply. Single quote didn't work.
Thank you
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])
vAgreementDate is defined as =Date#('01/01/2014', 'MM/DD/YYYY')
Please refer to this thread; I tried to explain in steps how to handle time expressions:
Hope this helps
Thanks Omar, that really helped in under standing the time expressions
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)
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.