Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
mahitham
Contributor II

How to show Top Employees

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 Top Employee is working fine when there is only one top employee but in Electronics Department there are 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.

2 Solutions

Accepted Solutions
Highlighted

Re: How to show Top Employees

May be try this

Employee

Concat(DISTINCT Aggr(If(Max(TOTAL <Department> Aggr(Count({<MonthDiff = {0}>} Trades), Department, Employee)) = Count({<MonthDiff = {0}>} Trades), Employee), Department, Employee), ',')

 

Count

Max(TOTAL <Department> Aggr(Count({<MonthDiff = {0}>} Trades), Department, Employee))

 

View solution in original post

Highlighted

Re: How to show Top Employees

So can you not do this? 

If(Len(Trim(Expression)) = 0, '-', Expression)

View solution in original post

7 Replies
Highlighted

Re: How to show Top Employees

May be try this

Employee

Concat(DISTINCT Aggr(If(Max(TOTAL <Department> Aggr(Count({<MonthDiff = {0}>} Trades), Department, Employee)) = Count({<MonthDiff = {0}>} Trades), Employee), Department, Employee), ',')

 

Count

Max(TOTAL <Department> Aggr(Count({<MonthDiff = {0}>} Trades), Department, Employee))

 

View solution in original post

Highlighted
mahitham
Contributor II

Re: How to show Top Employees

Hi @sunny_talwar 

Thanks a lot for your help.

I need small help here

In the Employee expression if there is no top employee in Department is it possible to show lik null (-) so that row can be deleted by Addon -- Data handling in pivot table.

For example : In Groceries Department there is no Top employee then it has to show like below. By using present expression not getting null under Employee when there is no employees under department.

 

Department      Employee       Count

Groceries              -                           -

Concat(DISTINCT Aggr(If(Max(TOTAL <Department> Aggr(Count({<MonthDiff = {0}>} Trades), Department, Employee)) = Count({<MonthDiff = {0}>} Trades), Employee), Department, Employee), ',')

Please help me on this.

Thanks in advance

Highlighted

Re: How to show Top Employees

Within Groceries, I thought Deepak was the top employee? Why is he not the top employee anymore?

Highlighted
mahitham
Contributor II

Re: How to show Top Employees

Hi @sunny_talwar 

Thanks for your response.

In real data in some of the Departments I don't have any Top Employees 

For example:

Department        Employee      Count

Electronics        Raj,Suraj            100

Groceries            Deepak               45

Clothes                                                 - 

Here on Clothes Department there is no Employees and no count so I have to delete that row completely.

By using concat expression not getting null(-) symbol under Clothes Employee when its null and count is null - then I can able to delete that row by Addon> data handling.

 

Highlighted

Re: How to show Top Employees

If it doesn't show null... what does it show? Can you share a sample qvf file where we can see the issue?

Highlighted
mahitham
Contributor II

Re: How to show Top Employees

Hi @sunny_talwar 

Its showing blank cell  instead of  - 

Highlighted

Re: How to show Top Employees

So can you not do this? 

If(Len(Trim(Expression)) = 0, '-', Expression)

View solution in original post