12 Replies Latest reply: Dec 9, 2013 9:25 AM by Mark campbell

# Set Analysis max date

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

• ###### Re: Set Analysis max date

hi

try this

only({<\$DATE={"\$(=max(Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY')))"}>} MTD)

• ###### Re: Set Analysis max date

that didn't work, same as before...  What are the double quotes for?

• ###### Re: Set Analysis max date

hi

try this

firstsorted(MTD,-Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY'))

• ###### Re: Set Analysis max date

I like that idea, but still null

• ###### Re: Set Analysis max date

try this

sum(Distinct  {<\$DATE={\$(=max(\$DATE))}>} MTD)

or this one

Sum(Distinct  {<\$DATE={"\$(=max(Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY')))"}>} MTD)

• ###### Re: Set Analysis max date

sadly neither of those work

• ###### Re: Set Analysis max date

TRY THIS

acct,

date(max(DATE))   as maxDate,

FirstSortedValue(mtd,-DATE)  as MTD

group by acct;

fact:

load *, date(date#(date,'DD-MM-YYYY'),'DD-MM-YYYY') AS DATE 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

]

SEE ATTACHMENT

• ###### Re: Set Analysis max date

that won't work as the date range changes as the users select different dates.

• ###### Re: Set Analysis max date

You need to convert your date string at script level....

date( subfield(DATE,'-',3') & '-' & subfield(DATE,'-',1') & '-' & subfield(DATE,'-',2') ) as NewDate

Change set statement to......

sum({\$<NewDate={\$(=max(NewDate))}>} MTD)

• ###### Re: Set Analysis max date

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!

• ###### Re: Set Analysis max date

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

• ###### Re: Set Analysis max date

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: