Set Analysis max date

[#1]

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

[jerem1234]

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!

[a_mullick]

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

[#4]

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)