Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

Max of Employee Names

Hi Experts,

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

Input Table.png

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

Table 2.png

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.

7 Replies
uacg0009
Partner - Specialist
Partner - Specialist

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

Hi @uacg0009

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

jaibau1993
Partner - Creator III
Partner - Creator III

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

May be like this?

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

=Max(Count)

 

Capture.PNG

jaibau1993
Partner - Creator III
Partner - Creator III

Nice one!

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

Jaime.

tresesco
MVP
MVP

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

mahitham
Creator II
Creator II
Author

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.

Input Table.png