Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
May be -
=sum({<[A/C Type]={'Direct Staff Costs*'},
FinResultsDate={"$(='>=' & date(makedate(2016,09,01),'MMM-YY')&'<='& date(max(FinResultsDate),'MMM-YY'))"}>} [USD amount])
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 |
What format FinResultsDate you have in your data?
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])
Still getting 0.
FinResultDate is in the format MMM-YY
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])
Thanks, This worked