topic Re: FirstSortedValue()_Multiple dimension values with same count in QlikView

FirstSortedValue()_Multiple dimension values with same sort order

joydipp1988 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same count

swuehl — Tue, 29 Mar 2016 19:13:38 GMT

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.

Re: FirstSortedValue()_Multiple dimension values with same count

joydipp1988 — Tue, 29 Mar 2016 19:22:28 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same count

swuehl — Tue, 29 Mar 2016 19:46:25 GMT

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?

Re: FirstSortedValue()_Multiple dimension values with same count

joydipp1988 — Tue, 29 Mar 2016 20:07:55 GMT

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 ?

Re: FirstSortedValue()_Multiple dimension values with same count

swuehl — Tue, 29 Mar 2016 20:54:07 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same count

joydipp1988 — Tue, 29 Mar 2016 20:59:50 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same count

tresB — Wed, 30 Mar 2016 11:41:12 GMT

Try this:

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

Re: FirstSortedValue()_Multiple dimension values with same count

swuehl — Wed, 30 Mar 2016 11:42:56 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same sort order

tresB — Wed, 30 Mar 2016 11:47:32 GMT

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.

Re: FirstSortedValue()_Multiple dimension values with same sort order

swuehl — Wed, 30 Mar 2016 11:54:29 GMT

You may be right. I haven't really understood what the OP is after.

Re: FirstSortedValue()_Multiple dimension values with same sort order

joydipp1988 — Wed, 30 Mar 2016 12:24:33 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same sort order

tresB — Wed, 30 Mar 2016 13:00:54 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same sort order

joydipp1988 — Sat, 02 Apr 2016 14:37:51 GMT

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

Re: FirstSortedValue()_Multiple dimension values with same count

luizcdepaula — Tue, 31 Jan 2017 19:17:42 GMT

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?

Re: FirstSortedValue()_Multiple dimension values with same sort order

palo173 — Mon, 11 Dec 2023 14:23:16 GMT

Hi @tresB,

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

Re: FirstSortedValue()_Multiple dimension values with same sort order

tresB — Tue, 12 Dec 2023 14:28:49 GMT

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.

Re: FirstSortedValue()_Multiple dimension values with same sort order

palo173 — Wed, 13 Dec 2023 09:02:49 GMT

Thank you for reaction @tresB . 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

Re: FirstSortedValue()_Multiple dimension values with same sort order

tresB — Wed, 13 Dec 2023 16:10:42 GMT

Try the below expression:

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

Re: FirstSortedValue()_Multiple dimension values with same sort order

palo173 — Thu, 14 Dec 2023 08:03:37 GMT

@tresB 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 '|');