Max of Employee Names

[mahitham]

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.

[uacg0009]

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

[mahitham]

Hi @uacg0009

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

[jaibau1993]

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. 

[tresesco]

May be like this?

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

=Max(Count)

[jaibau1993]

Nice one!

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

Jaime.

[tresesco]

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

[mahitham]

Hi @tresesco 

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.