Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ritnoa
Contributor
Contributor

Calculating employee reporting levels to their respective managers from employee table

Ritnoa_0-1591944362183.png

In the above image need to write a script to calculate the number of employees reporting to their respective managers including their Level. So we have 9 levels here showing number of employees reporting in each level.

E.g. Level3 has 1 employee reporting

Level4 has 11 employees reporting

Columns are 

Employee_ID   Employee_Name    LineManager_ID    LineManager_Name   Emp_Band   

3 Solutions

Accepted Solutions
jwjackso
Specialist III
Specialist III

Probably an easier way, but I used the HierarchyBelongsTo with the data below:

Data:
Load * Inline [
LineManager_ID,Employee_ID,Employee_Name
P1,P2,'N2'
P2,P3,'N3'
P2,P4,'N4'
P2,P5,'N5'
P3,P6,'N6'
P3,P7,'N7'
P4,P8,'N8'
P4,P9,'N9'
P5,P10,'N10'
P5,P11,'N11'
P10,P12,'N12'
];

PC_Tree:
HierarchyBelongsTo(Employee_ID,LineManager_ID,Employee_Name,AncestorID,AncestorEmployee_Name,DepthID)
Load LineManager_ID,
Employee_ID,
Employee_Name
Resident Data;

PC_Tree_Temp:
load max(DepthID) as MaxDepthID
Resident PC_Tree;

Let vMaxDepthID=Peek('MaxDepthID',0,'PC_Tree_Temp');

 

Capture.PNG

View solution in original post

Saravanan_Desingh

I forgot to share the expression.

%

Sum(Volume)/Sum(TOTAL Volume)

2nd%

-Sum(Volume)/Sum(TOTAL Volume)

3rd

Background Color: (for % and 2nd%)

Color(2)

 

View solution in original post

7 Replies
jwjackso
Specialist III
Specialist III

Probably an easier way, but I used the HierarchyBelongsTo with the data below:

Data:
Load * Inline [
LineManager_ID,Employee_ID,Employee_Name
P1,P2,'N2'
P2,P3,'N3'
P2,P4,'N4'
P2,P5,'N5'
P3,P6,'N6'
P3,P7,'N7'
P4,P8,'N8'
P4,P9,'N9'
P5,P10,'N10'
P5,P11,'N11'
P10,P12,'N12'
];

PC_Tree:
HierarchyBelongsTo(Employee_ID,LineManager_ID,Employee_Name,AncestorID,AncestorEmployee_Name,DepthID)
Load LineManager_ID,
Employee_ID,
Employee_Name
Resident Data;

PC_Tree_Temp:
load max(DepthID) as MaxDepthID
Resident PC_Tree;

Let vMaxDepthID=Peek('MaxDepthID',0,'PC_Tree_Temp');

 

Capture.PNG

Ritnoa
Contributor
Contributor
Author

Thanks for the solution, I got 89k records and actual records are 11,196.However I tried using Hierarchy function it worked. 

HR:

Load*From HR.xlsx

OE:
Hierarchy (Emp_ID, Manager_ID,Layer)
LOAD
Global_Employee AS Emp_ID,
Line_Manager AS Manager_ID,
[Global Employee] AS Layer

Resident HR;

So i already go the layers, i.e. L1, L2, L3 through Hierarchy function .... i need to use them to create the below chart. Any suggestions.

 

Regards,

Sameer.

Saravanan_Desingh

Like this?

commQV57.PNG

Ritnoa
Contributor
Contributor
Author

Perfecto !!!

Could you please let me know how to achieve this.

Regards,

Sameer

Saravanan_Desingh

Try this.

commQV61.PNGcommQV62.PNGcommQV63.PNG

Saravanan_Desingh

I forgot to share the expression.

%

Sum(Volume)/Sum(TOTAL Volume)

2nd%

-Sum(Volume)/Sum(TOTAL Volume)

3rd

Background Color: (for % and 2nd%)

Color(2)

 

Saravanan_Desingh

Input data:

tab1:
LOAD * INLINE [
    Level, Volume
    L1, 0
    L2, 0
    L3, 1
    L4, 11
    L5, 31
    L6, 436
    L7, 1484
    L8, 1008
    L9, 65
];