sum to current date selection

[mogaka1234]

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?

[Anil_Babu_Samineni]

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?

[Digvijay_Singh]

May be -

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

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

[mogaka1234]

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

[Digvijay_Singh]

What format FinResultsDate you have in your data?

[Digvijay_Singh]

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

[mogaka1234]

Still getting 0.

[mogaka1234]

FinResultDate is in the format MMM-YY

[Kushal_Chawda]

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

[mogaka1234]

Thanks, This worked