Problem with FirstSortedValue when dealing with repeated values

[#1]

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

[arulsettu]

HI

try this

fist text box

FirstSortedValue(col1,aggr(sum(col3),col2,col1))

second text box

FirstSortedValue(col3,-aggr(sum(col3),col2,col1))

[tresesco]

Exp1: =FirstSortedValue(DISTINCT col2, -col1)

Exp2: =FirstSortedValue(Aggr(concat(DISTINCT col3, ','), col1), -Aggr(col1,col1))

[effinty2112]

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

[Kushal_Chawda]

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,',')

[sasiparupudi1]

use distinct qualifier in your firstsortedvalue

FirstSortedValue(distinct col2,-col1)

FirstSortedValue(distinct col3, -col1)

hth

Sasi