How to show Top Employees

[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 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.

[sunny_talwar · Accepted solution]

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

[mahitham]

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

[sunny_talwar]

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

[mahitham]

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.

[sunny_talwar]

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

[mahitham]

Hi @sunny_talwar 

Its showing blank cell  instead of  - 

[sunny_talwar · Accepted solution]

So can you not do this? 

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