Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
christophebrault
Specialist
Specialist

Getting Value for the last Date

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 :

DATEDATE_ENVVALUE

01/01/2014

01/01/201412
01/01/201402/01/201414
01/01/201403/01/201411
01/01/201404/01/201413
01/01/201405/01/201412

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

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
1 Solution

Accepted Solutions
jonathandienst

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

MK_QSL
MVP
MVP

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;

Not applicable

This should hopefully work. I'd test it though

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

Nicole-Smith

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.