Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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.