topic Re: Max of Employee Names in QlikView

Max of Employee Names

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

Hi Experts,

Can any one please help me on below requirement. I have a Table like below

From the above table need to show top Employee Names by Department and Top Employee count by Department like below table

I have tried below expressions the First expression for Employee is working fine when there is only one top employee but in Electronics Department there is Two Top Employees Named Raj and Suraj there getting null instead of there names, in Groceries its working fine.

Please help me to modify the below Employee Expression for showing all Top Employees like above 2nd table.

Employee:

FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee, 
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee))

Count:
Max({<Employee={"=len(trim(Employee))>0"}>} 
Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee))

Thanks in advance.

Re: Max of Employee Names

uacg0009 — Mon, 06 May 2019 05:37:38 GMT

Hi Mahitham,
I think you need to try to use rank() = 1 to get the user name list, then concat them, like you said, firstsortedvalue will have problem.
Please try.
Aiolos Zhao

Re: Max of Employee Names

mahitham — Mon, 06 May 2019 06:59:11 GMT

Hi @uacg0009

Could you please provide the expression with rank() based on my expression . I am new to rank()

Re: Max of Employee Names

jaibau1993 — Mon, 06 May 2019 07:08:48 GMT

Hi!

As Aiolos sugest, the following expression worked for me (I used a simplified data model):

Concat(Aggr( if(rank(Sum(Count), 1)=1, Only(Employee)) ,Department, Employee ), ', ')

This espression is used inside a simple table with only department as dimension.

Bests,

Jaime.

Re: Max of Employee Names

tresB — Mon, 06 May 2019 07:30:22 GMT

May be like this?

=FirstSortedValue( Aggr(Concat(DISTINCT Employee, ','), Department, Count), -Aggr(Count, Count))

=Max(Count)

Re: Max of Employee Names

jaibau1993 — Mon, 06 May 2019 07:26:25 GMT

Nice one!

Isn't equivalent the second FirstSortedValue to Max(Count)? 🙂

Jaime.

Re: Max of Employee Names

tresB — Mon, 06 May 2019 07:29:28 GMT

😋sometimes one can do this!! Thanks..corrected above.

Re: Max of Employee Names

mahitham — Mon, 06 May 2019 09:06:40 GMT

Hi @tresB

Thanks for your help,.

Could you please help me to insert these expression in your expression.

Here count is not the dimensions its an expression i.e., Count({<MonthDiff={0}>} Trades).

This table has some null Employees also those null employee count is high so need to exclude in the expression like below.

FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee,
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee))

1month back in the below post you have suggested the above expression. Its working fine but I got multiple employees with top count so I am looking for new expression in the same requirement.

https://community.qlik.com/t5/New-to-QlikView/Max-of-trades-help/m-p/1553550#M371298

Please help me on this.

Thanks in advance.