Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
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
Partner

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

Hi @uacg0009

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

jaibau1993
Partner
Partner

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
Partner

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

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