Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to return a value where date=max(date); however, I can't seem to get the function designed quite right. I have tried multiple options, I'll list a few below that didn't seem to work:
only({<$DATE={$(=max($DATE))}>} MTD) :: returns nulls
sum({<$DATE={$(=max($DATE))}>} MTD) :: returns 10.5 for acct A
fact:
load * inline [
acct,date,mtd
A,1-1-2013,3.4
A,1-2-2013,3.5
A,1-3-2013,3.6
B,1-1-2013,3.2
B,1-2-2013,2.8
]
chart:
dim=acct:
acct | mtd where date=max(date)
A | 3.6
B | 2.8
Try this:
subfield(concat(MTD, ',', -date#(DATE, 'M-D-YYYY')), ',', 1)
or if your date field is already in a date format:
subfield(concat(MTD, ',', -DATE), ',', 1)
Hope this helps!
Hi,
Can you have a look at this and see if it works for you. I've changed the date format to my default, but hopefully it should work ok with your dates...
Thanks,
Azam
eventually I found out what the problem was. using a concat function to debug the underlying values is super helpful. I had a secondary table that I needed to exclude the filter on, which was causing all of your correct suggestions to not work in my implementation. eventually, I did the below to get what i needed:
only({<PerfReturnPeriods_Code=,PerfReturnPeriods_Desc=,BUSINESS_DATE_PERF={"$(=max(BUSINESS_DATE_PERF))"}>} TOTAL RETURN_MTD)