Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Set Analysis max date

hi

try this

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

12 Replies
Not applicable

Re: Set Analysis max date

hi

try this

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

Not applicable

Re: Set Analysis max date

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

Not applicable

Re: Set Analysis max date

hi

try this

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

Not applicable

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)

Not applicable

Re: Set Analysis max date

I like that idea, but still null

Not applicable

Re: Set Analysis max date

sadly neither of those work

Not applicable

Re: Set Analysis max date

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

Not applicable

Re: Set Analysis max date

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

wallinpeter
New Contributor III

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)