picking the latest date of a value exceeding a number

[firmographs]

We are trying to configure Qlik to help us pick a most-recent date when a value was exceeded.  

I'm attaching a sample dataset.  

In this data, we are interested in values that exceed 0.015.  

For the most-recent (latest date) that a value of 0.015 was exceeded, we want to display the date and the value.  

The correct answer is 2004-12-31, with the value of 0.02.  

How can I display the date and value associated with that date, either by using the load script to create a latest date, or by configuring a measure to do this?  I think I'd prefer to handle it in the load script.  

[sunny_talwar]

Try something like this

FirstSortedValue(Date, -Value)

[firmographs]

This seems like part of the answer.  How do we have it pick from only those that exceed our limit to begin with?

[Kushal_Chawda]

try below

FirstSortedValue(distinct Date, -if(Value>0.015,Value))

FirstSortedValue(distinct Value, -if(Value>0.015,Value))

[firmographs]

I think we are getting close.  This returns the first time that the limit was exceeded.  We need the last time.  

[firmographs]

Is there an equivalent way to do 'lastsortedvalue'?

[Kushal_Chawda · Accepted solution]

try below

FirstSortedValue(distinct Date, -if(Value>0.015,Date))

FirstSortedValue(distinct Value, -if(Value>0.015,Date))

[RsQK]

Heres an option.

[firmographs · Accepted solution]

Kush, this works.  Changing the last argument in the expression from 'value' to 'date' did the trick.  Thanks!

[Kushal_Chawda]

Please mark appropriate answer as correct