Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

picking the latest date of a value exceeding a number

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.  

Labels (1)
2 Solutions

Accepted Solutions
Highlighted
MVP
MVP

try below

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

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

View solution in original post

Highlighted
Partner
Partner

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

View solution in original post

10 Replies
Highlighted

Try something like this

FirstSortedValue(Date, -Value)
Highlighted
Partner
Partner

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

Highlighted
MVP
MVP

try below

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

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

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

Highlighted
Partner
Partner

Is there an equivalent way to do 'lastsortedvalue'?

Highlighted
MVP
MVP

try below

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

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

View solution in original post

Highlighted
Contributor III
Contributor III

Heres an option.

Highlighted
Partner
Partner

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

View solution in original post

Highlighted
MVP
MVP

Please mark appropriate answer as correct