Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
joydipp1988
Creator
Creator

FirstSortedValue()_Multiple dimension values with same sort order

Dear all,

Following is the schema of my data.

EmployeeIDAgeConsumptionEducationGenderYearlyIncome

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try this:

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

View solution in original post

14 Replies
swuehl
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

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
Creator
Creator
Author

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
MVP
MVP

Try this:

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

View solution in original post

swuehl
MVP
MVP

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

tresesco
MVP
MVP

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.