Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Can someone guide me how to do this(mentioned below) type of calculations ?
Input Data Format for any month
Employee ID | Level |
---|---|
101 | EE |
102 | EE |
103 | EE |
104 | TL |
105 | TL |
106 | EE |
107 | EE |
108 | EE |
109 | EE |
110 | AM |
111 | MGR |
Seniority in level : MGR>AM >TL> EE
How i want to display Output table in QlikSense
Level | Headcount | Supervisory Ratio | Span of Control |
---|---|---|---|
MGR | 1 | 1 | 7 |
AM | 1 | 2 | 7 |
TL | 2 | 3.5 | 3.5 |
EE | 7 | ||
Supervisory Ratio for TL : Count Level(EE)/Count (TL)
Supervisory Ratio for AM : Count Level(TL)/Count (AM)
Supervisory Ratio for MGR : Count Level(AM)/Count (MGR)
Span of Control for AM : Count Level(EE)/Count (AM)
Span of Control for TL : Count Level (EE)/ Count(TL)
Span of Control for TL : Count Level (EE)/ Count(MGR)
For Span of control - Numerator will always be count Level( EE)
Thanks
Anurag Gupta
I would add another field to your source to assign the hierarchy for each Level, then you can use the same field for any of your calculations above.
Hi Kris,
If i am correct - I need to add one more column (Next level) to my source data,wherein for employee at EE Level , Next level will be TL annd so on ..
What do i do next ?
Thanks
Anurag Gupta
That is correct. You will use this field to define your aggregation level for your supervisor ratio. You shouldn't need anything else for your Span of Control field, as you can use your existing fields and just include the logic. If you're new to Qlik products I would lean on the Help features if you need it with your expressions. Good luck!
Thanks Kris,
It will be great if you can help me with expressions also. I have just started using qliksense.
Regards
Anurag Gupta
Hello Anurag,
Try this,
Creat a straight table.
Dimension = Level
Expression :
1)For Headcount:
Count(Level)
2)Supervisory Ratio:
If(Level='TL', (count({<Level={'EE'}>}Level)/
count({<Level={'TL'}>}Level)),
If(Level='AM', (count({<Level={'TL'}>}Level)/
count({<Level={'AM'}>}Level)),
If(Level='MGR', (count({<Level={'AM'}>}Level)/
count({<Level={'MGR'}>}Level))
)))
3) Span of control :
If(Level='AM', (count({<Level={'EE'}>}Level)/
count({<Level={'AM'}>}Level)),
If(Level='TL', (count({<Level={'EE'}>}Level)/
count({<Level={'TL'}>}Level)),
If(Level='MGR', (count({<Level={'EE'}>}Level)/
count({<Level={'MGR'}>}Level))
)))
Hope this helps!