How to select Select Next Row ?

[#1]

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

[#2]

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.

[#3]

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

[#4]

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!

[#5]

Thanks Kris,

It will be great if you can help me with expressions also. I have just started using qliksense.

Regards

Anurag Gupta

[rohitraut]

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!