Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to return smallest value if FirstSortedValue finds two values?

Hey guys,

I've encountered the following problem. My expression FirstSortedValue(Amount, -Date) sometimes returns NULL because there are two or more values found for a specific Date. Now I could use DISTINCT but I would have no control over which value is returned as it based on load order. I want to always return the smallest value while utilizing the ranking of FirstSortedValue. How can I achieve that?

 

Thanks.

1 Solution

Accepted Solutions
Highlighted

Re: How to return smallest value if FirstSortedValue finds two values?

May be this

FirstSortedValue(Amount, -(Date - (Amount/1E10)))

View solution in original post

4 Replies
Highlighted
Master II
Master II

Re: How to return smallest value if FirstSortedValue finds two values?

min(FirstSortedValue(Amount, -Date))

Highlighted

Re: How to return smallest value if FirstSortedValue finds two values?

May be this

FirstSortedValue(Amount, -(Date - (Amount/1E10)))

View solution in original post

Highlighted
Contributor II
Contributor II

Re: How to return smallest value if FirstSortedValue finds two values?

Hey guys, thanks for the quick replies. 

Min(FirstSortedValue, ..., ...)) can not work because nested aggregations are not allowed. 

 

FirstSortedValue(Amount, -(Date - (Amount/1E10))) seems to work fine, however I don't quite understand the reason behind this. Would you be able to explain your solution?

 

Thanks.

 

Highlighted

Re: How to return smallest value if FirstSortedValue finds two values?

I am not just looking at the value on the Max Date, but Max Date where Value is the minimum. So, if the max date is 43859 and there are two values associated with it (100 and 200)... the second part of the FirstSortedValue will look not just by 43859, but 43859 - (100/10,000,000,000) and 43859 - (200/10,000,000,000)

Based on the two new values, the max will be the Amount associated with 43859 - (100/10,000,000,000)