topic Re: Set Analysis max date in QlikView

Set Analysis max date

Thu, 05 Dec 2013 19:23:15 GMT

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

Thu, 05 Dec 2013 19:29:24 GMT

try this

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

Re: Set Analysis max date

Thu, 05 Dec 2013 19:34:00 GMT

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

Re: Set Analysis max date

Thu, 05 Dec 2013 19:37:31 GMT

try this

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

Re: Set Analysis max date

Thu, 05 Dec 2013 19:39:53 GMT

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

Thu, 05 Dec 2013 19:40:55 GMT

I like that idea, but still null

Re: Set Analysis max date

Thu, 05 Dec 2013 19:43:59 GMT

sadly neither of those work

Re: Set Analysis max date

Thu, 05 Dec 2013 19:52:18 GMT

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

Re: Set Analysis max date

Thu, 05 Dec 2013 19:54:02 GMT

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

Re: Set Analysis max date

wallinpeter — Thu, 05 Dec 2013 20:12:25 GMT

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

jerem1234 — Fri, 06 Dec 2013 15:07:31 GMT

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

a_mullick — Mon, 09 Dec 2013 14:19:51 GMT

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

Mon, 09 Dec 2013 14:25:41 GMT

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)