11 Replies Latest reply: May 26, 2017 6:10 AM by sonygot g

# sum to current date selection

i am trying to add expenditure to current selection using: =sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=\$(=date('01/09/2016','MMM-YY')) <=\$(=date(max(FinResultsDate),'MMM-YY'))'}>} [USD amount])

My year starts in september. Anyhelp?

• ###### Re: sum to current date selection

You mean to say this?

=sum({<expenditure=, [A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=\$(=date('01/09/2016','MMM-YY')) <=\$(=date(max(FinResultsDate),'MMM-YY'))'}>} [USD amount])

Or Is there any error you are seeing?

• ###### Re: sum to current date selection

May be -

=sum({<[A/C Type]={'Direct Staff Costs*'},

FinResultsDate={"\$(='>=' & date(makedate(2016,09,01),'MMM-YY')&'<='& date(max(FinResultsDate),'MMM-YY'))"}>} [USD amount])

• ###### Re: sum to current date selection

I am getting zeros:

 FinResultDate Cost GL A/C Type =sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={">=Sep-16<=Mar-17"}>} [USD amount]) Apr 17 10.00 Direct Staff Costs 0 Mar 17 11.00 Direct Staff Costs 0 Feb 17 13.00 Direct Staff Costs 0 Jan 17 17.00 Direct Staff Costs 0 Dec 16 2,001.00 Direct Staff Costs 0 Nov 16 11.00 Direct Staff Costs 0 Oct 16 14.00 Direct Staff Costs 0 Sep 16 2.00 Direct Staff Costs 0
• ###### Re: sum to current date selection

What format FinResultsDate you have in your data?

• ###### Re: sum to current date selection

Try to avoid date specific formatting inside set expression as far as possible, check this as well -

=sum({<[A/C Type]={'Direct Staff Costs*'},

FinResultsDate={"\$(='>=' &chr(39)& date(makedate(2016,09,01),'MMM-YY')&chr(39)&'<='& chr(39)&date(max(FinResultsDate),'MMM-YY'))&chr(39)"}>} [USD amount])

• ###### Re: sum to current date selection

Still getting 0.

• ###### Re: sum to current date selection

FinResultDate is in the format MMM-YY

• ###### Re: sum to current date selection

Make sure that FinResultsDate is in dual format, to check put =max(FinResultsDate) in text object if it returns number then it is in dual format. If so below expression should work

sum({<[A/C Type]={'Direct Staff Costs*'}, FinResultsDate={'>=\$(=date(date#('01/09/2016','DD/MM/YYYY'),'MMM-YY'))<=\$(=max(FinResultsDate))'}>} [USD amount])

• ###### Re: sum to current date selection

Thanks, This worked