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 may be right. I haven't really understood what the OP is after.

[joydipp1988]

Dear Sir,

Thanks for the quick reply. The expression you suggest is actually what I wanted. I got the output. But I'm not getting its flow of execution.

First part of the expression I understood i.e. Aggr(Concat(DISTINCT EmployeeID, ','),YearlyIncome)

But next part i.e. sort weight part -Aggr(YearlyIncome,YearlyIncome) I'm not getting. Why you did that? Please explain the flow. Thanks in advance.

Regards,

Joy

[tresesco]

-Aggr(YearlyIncome,YearlyIncome) - is to tell qv explicitly that consider the aggregation level at YearlyIncome . Note, this aggr() is only required when there are multiple occurrences of the same dimension values with maximum ranked values calculated.

That means, had there not been multiple EmployeeIDs ,

the epression:

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

would work fine. Hope this helps.

[joydipp1988]

Dear tresesco‌ and swuehl‌,

Thanks for your sincere reply. I got my desired answer from your replies. But I've one last query on this topic. Till now we have got three exact solutions of this problem. They are as follows-

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

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

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

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

So my question is which one is the best solution for huge (millions) of data and why? Thanks in advance.

Regards,

Joy

[luizcdepaula]

Hi SWuhl,

Is there a way to accomplish the same result in the script? As AGGR is not a script function.

I have to create a separate table in order to achieve the results I need, and they include top results based on Vendor and Month.

Any ideas?

LD

[palo173]

Hi @tresesco,

could you help me with correct my expression?

=FirstSortedValue(Aggr(Concat(DISTINCT [Brand], ','), ??? ),-(Aggr(Sum({< Fakt = {'Fakt_A'}, year = {'2023'}>}[#Reg]), [Area],[Brand])))

I am trying to find the correct part of the code instead of ??? but without success. I have already tried everything, except the correct result of course.

Thank you
Palo

[tresesco]

Hi @palo173 , I would suggest to create a sample qvf and explain your expected output w.r.t. that sample data and context. Share the qvf here or create a new thread; that's a better chance for you towards solution.

[palo173]

Thank you for reaction @tresesco . I apologize for missing  explanation. I use excel for it.
Source data are in columns A:E.
Pivot with conditions and calculations are in columns H:J.
And result is in M4:N7. I tried to find Bands or multiple Brands, which have the most Reg in Area.

=FirstSortedValue(Aggr(Concat(DISTINCT [Brand], ','), ??? ),-(Aggr(Sum({< Fakt = {'Fakt_A'}, year = {'2023'}>}[#Reg]), [Area],[Brand])))

Thank you
Palo

[tresesco]

Try the below expression:

=Concat(distinct Aggr(If(Sum(Reg) = Max(total Aggr(Sum(Reg),
Area, Brand)), Brand), Area, Brand), ',')

[palo173]

@tresesco  It works without parameters, but when I add "year" and "Fakt", it is not right. Please, what is wrong? 

=Concat(distinct Aggr(If(Sum({< Fakt = {'Fakt_A'}, Rok = {'2023'}>}Reg) = Max(total Aggr(Sum({< Fakt = {'Fakt_A'}, Rok = {'2023'}>}Reg), Area, Brand)), Brand), Area, Brand), ',')

the correct result should be:

Area	Brands
A	B1, B2
B	B1
C	B1

LOAD * inline [
Fakt|year|Area|Brand|Reg
Fakt_A|2023|A|B1|1
Fakt_A|2023|A|B1|1
Fakt_A|2023|A|B2|1
Fakt_A|2023|A|B2|1
Fakt_A|2023|B|B1|1
Fakt_A|2023|B|B1|1
Fakt_A|2023|B|B2|1
Fakt_A|2023|C|B1|1
Fakt_A|2022|C|B2|1
Fakt_A|2022|C|B2|1
Fakt_A|2022|C|B2|1
Fakt_B|2023|A|B1|1
Fakt_B|2023|A|B2|1
Fakt_B|2022|B|B1|1
] (delimiter is '|');