Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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 II
Specialist II

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

saran7de
Master
Master

saran7de
Master
Master

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 II
Specialist II

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

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.

saran7de
Master
Master

Like this?

commQV57.PNG

Ritnoa
Contributor
Contributor
Author

Perfecto !!!

Could you please let me know how to achieve this.

Regards,

Sameer

saran7de
Master
Master

saran7de
Master
Master

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

saran7de
Master
Master

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
];