Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a problem with FirstSortedValue. Please help.
temp:
load * inline
[
col1, col2, col3
1, a1, 1000
2,c1, 2000
3, b1, 3000
3,b1,4000
];
I have data as above. And in a text box I have =FirstSortedValue( col2, -col1), The result i want is b1 but it does not work.
In another box I have =FirstSortedValue( col3, -col1), The result I want is 3000, 4000 but it does not work either.
Can anybody help me? Thank you in advance
Best,
Junfei
HI
try this
fist text box
FirstSortedValue(col1,aggr(sum(col3),col2,col1))
second text box
FirstSortedValue(col3,-aggr(sum(col3),col2,col1))
Exp1: =FirstSortedValue(DISTINCT col2, -col1)
Exp2: =FirstSortedValue(Aggr(concat(DISTINCT col3, ','), col1), -Aggr(col1,col1))
Hi Junfei,
Here's an alternative for your second expression. I dislike FirstSortedValue because of the way it treats ties for the place in the ranking we're looking for
concat(Aggr(if(Rank(sum(col1)) =1, col3),col3),',') = 3000,4000
Cheers
Andrew
Your expression is correct, but you just need to add Distinct keyword as you have multiple value for max of col1
FirstSortedValue(DISTINCT col2, -col1)
for second expression you can use the below instead of complex firstsortedvalue function
=Concat({<col1={"$(=max(col1))"}>}col3,',')
use distinct qualifier in your firstsortedvalue
FirstSortedValue(distinct col2,-col1)
FirstSortedValue(distinct col3, -col1)
hth
Sasi