Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
try below
FirstSortedValue(distinct Date, -if(Value>0.015,Date))
FirstSortedValue(distinct Value, -if(Value>0.015,Date))
Kush, this works. Changing the last argument in the expression from 'value' to 'date' did the trick. Thanks!
Try something like this
FirstSortedValue(Date, -Value)
This seems like part of the answer. How do we have it pick from only those that exceed our limit to begin with?
try below
FirstSortedValue(distinct Date, -if(Value>0.015,Value))
FirstSortedValue(distinct Value, -if(Value>0.015,Value))
I think we are getting close. This returns the first time that the limit was exceeded. We need the last time.
Is there an equivalent way to do 'lastsortedvalue'?
try below
FirstSortedValue(distinct Date, -if(Value>0.015,Date))
FirstSortedValue(distinct Value, -if(Value>0.015,Date))
Heres an option.
Kush, this works. Changing the last argument in the expression from 'value' to 'date' did the trick. Thanks!
Please mark appropriate answer as correct