topic Getting Value for the last Date in QlikView

Getting Value for the last Date

christophebrault — Fri, 28 Mar 2014 09:31:00 GMT

Hi,

I can't achieve do to the following :

I have three fields :

DATE : The first day of the month

DATE_ENV : Its the date of the day

VALUE : My value field

My date Format is 'DD/MM/YYYY' as i'm french

Imagine my table :

DATE	DATE_ENV	VALUE
01/01/2014	01/01/2014	12
01/01/2014	02/01/2014	14
01/01/2014	03/01/2014	11
01/01/2014	04/01/2014	13
01/01/2014	05/01/2014	12

I want to get in a textbox the value for 05/01/2014 in DATE_ENV when i select 01/01/2014 in DATE. I Should have 12.

I've tired : Only({$<DATE_ENV={$(=Date(Max(DATE_ENV)))}>}VALUE) but it's not working and i don't understand why ?

Any idea and examples ?

Thanks

Re: Getting Value for the last Date

jonathandienst — Fri, 28 Mar 2014 09:45:32 GMT

Use FirstSortedValue() like this with DATE selected:

=FirstSortedValue(DATE_ENV, -DATE_ENV) // for the most recent date

=FirstSortedValue(VALUE, -DATE_ENV) // for the value on the most recent date

You can also use these expressions in a chart/table that has DATE as a dimension.

HTH

Jonathan

Re: Getting Value for the last Date

MK_QSL — Fri, 28 Mar 2014 09:48:09 GMT

Try something like below in Script and create a list box

=Only(LastValue)

DATA:

Load * Inline

[

DATE, DATE_ENV, VALUE

01/01/2014, 01/01/2014, 10

01/01/2014, 02/01/2014, 11

01/01/2014, 03/01/2014, 12

01/01/2014, 04/01/2014, 13

01/01/2014, 05/01/2014, 14

01/02/2014, 01/02/2014, 100

01/02/2014, 02/02/2014, 110

01/02/2014, 03/02/2014, 120

01/02/2014, 04/02/2014, 130

01/02/2014, 05/02/2014, 140

];

Join

Load

DATE,

FirstSortedValue(VALUE, -DATE_ENV) as LastValue

Resident DATA

Group BY DATE;

Re: Getting Value for the last Date

Fri, 28 Mar 2014 10:17:20 GMT

This should hopefully work. I'd test it though

=FieldValue('VALUE', FieldIndex('DATE_ENV', DATE(MAX(DATE_ENV),'DD/MM/YYYY')))

Re: Getting Value for the last Date

Nicole-Smith — Fri, 28 Mar 2014 13:10:38 GMT

Single quotes around the max date in your set analysis should do the trick:

=Only({<DATE_ENV={'$(=Date(Max(DATE_ENV)))'}>}VALUE)

Example file is also attached.