FirstSortedValue()_Multiple dimension values with same sort order

[joydipp1988]

Dear all,

Following is the schema of my data.

EmployeeID

Age

Consumption

Education

Gender

YearlyIncome

I want to show EmployeeID, Age, Gender in different text objects who have highest YearlyIncome. Previously I have data (Sheet 1) where there was only one Max value. I solved this problem by using FirstSortedValue() function. But in the current data (Sheet 2) I've two EmployeeID which are sharing the same maximum YearlyIncome. Please find the attached data set and qvw application. I solved this issue by following two approaches. Here are they-

Approach1: Concat(If(Aggr(Sum(YearlyIncome),EmployeeID)=vMaxSal,EmployeeID),',')

Approach2: Concat( {1<EmployeeID=P({<YearlyIncome={$(vMaxSal)}>})>} EmployeeID,',')

vMaxSal: =Max(Aggr(Sum(YearlyIncome),EmployeeID))

Although I've already solved the problem but still I've the following questions:

1) Can this problem be solved by using FirstSortedValue() in UI ? If Yes then how, if not then why No?

2) Can this problem be solved by using FirstSortedValue() in script ? If Yes then how, if not then why No?

3) Which approach is most effective way for huge amount of data, Approach 1 or Approach 2 ?

Please find the app and data set and also I'm attaching script in notepad. Thanks in advance.

Regards,

Joy

[swuehl]

You can use the DISTINCT qualifier with FirstSortedValue() to get at least one result back, the first employee in load order.

If you want both, you need to use the Concat() function.

[joydipp1988]

Thanks for your quick reply. But I actually wanted to get both result. I tried FirstSortedValue() with Concat(). But couldn't make it. Can you please guide me how to do it?

Regards,

Joy

[swuehl]

Not sure if I understand, I thought you already got it working with your two approaches?

How do you want to get FirstSortedValue() into one of these approaches?

[joydipp1988]

I think you misunderstood. Actually I got my result with my two approaches. I don't want to merge FirstSortedValue() with my two approaches. I just wanted to know is it possible to get the same result by using FirstSortedValue() or not ? If possible then how ?

[swuehl]

I tried to answer this with my first answer to your thread, if you want to see both employees, you need to use Concat().

[joydipp1988]

Yaa I got your reply. Actually I was looking for a proper expression which use FirstSortedValue() function with Concat() function all will work for me. For this reason only I attached my qvw app. Anyway, thanks for your concern.

Regards,

Joy

[tresesco · Accepted solution]

Try this:

=FirstSortedValue( Aggr(Concat(DISTINCT EmployeeID, ','),YearlyIncome),-Aggr(YearlyIncome,YearlyIncome))

[swuehl]

Sure, but I can spot the Concat() function in there

[tresesco]

I am sure that this is something that you too wanted to communicate. I guess, it could be a communication-gap. I read:

Actually I was looking for a proper expression which use FirstSortedValue() function with Concat() function all will work for me.

and thought that let's check if this is what he wants.