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
hi
try this
only({<$DATE={"$(=max(Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY')))"}>} MTD)
hi
try this
only({<$DATE={"$(=max(Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY')))"}>} MTD)
that didn't work, same as before... What are the double quotes for?
hi
try this
firstsorted(MTD,-Date(Date#(DATE,'DD-MM-YYYY'),'DD-MM-YYYY'))
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)
I like that idea, but still null
sadly neither of those work
TRY THIS
LOAD
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
that won't work as the date range changes as the users select different dates.
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)