Getting Value for the last Date

[christophebrault]

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

[jonathandienst · Accepted solution]

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

[MK_QSL]

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;

[#4]

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.