Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
FabioP
Contributor II
Contributor II

How to get the last value for a specific field with null among values

Dear all,

I'm trying to get a specific value in table bellow:

DateValue
31-dez-183,87480
31-jan-194,25770
28-fev-193,65365
31-mar-193,74815
30-abr-194,15305
31-mai-193,93085
30-jun-193,99555
31-jul-194,15670
31-ago-193,79070
30-set-193,81860
31-out-194,21535
30-nov-194,03070
31-dez-193,87460
31-jan-204,25770
29-fev-203,99999
31-mar-20 
30-abr-20 
31-mai-20 
30-jun-20 
31-jul-20 
31-ago-20 
30-set-20 
31-out-20 
30-nov-20 
31-dez-20 


I'm looking for the last one in Value field => '3,99999'!

I tried to use this function: =FirstSortedValue(Value, -Value)
But the value that is returning to me is '-'


I'll use this in a Text Object...
Is just to show the value!


Could you guys help me?

1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

You can use this method:

=max({<Date={"$(=date(max({<Value={'>0'}>}Date)))"}>}Value)

max_value.jpg

First max() function - for cases when you have multiple values in output (You can use smth else like min() or only())

Second max in set analysis - for checking which date we should pick (value should be greater than zero)

And one more thing: make sure your Date field has the same format as the output of set analysis [date(max({<Value={'>0'}>}Date))]

View solution in original post

4 Replies
Sergey_Shuklin
Specialist
Specialist

Hello!

Where do you want to get it? On script load or on a front-end side?

FabioP
Contributor II
Contributor II
Author

I'm using a Text Object...
Is just to show the value!

tresesco
MVP
MVP

Try like:

 

=FirstSortedValue( {<Value={"=len(trim(Value))>0"}>}Value, -Date#(Date,'DD-MMM-YY'))

Sergey_Shuklin
Specialist
Specialist

You can use this method:

=max({<Date={"$(=date(max({<Value={'>0'}>}Date)))"}>}Value)

max_value.jpg

First max() function - for cases when you have multiple values in output (You can use smth else like min() or only())

Second max in set analysis - for checking which date we should pick (value should be greater than zero)

And one more thing: make sure your Date field has the same format as the output of set analysis [date(max({<Value={'>0'}>}Date))]