Count layers in a list?

OSUBlakester

Hi,

I am looking to count the number of layers (not descendants) in an employee hierarchy.

For example:

Manager 1 has 3 direct reports

Mananger 2 reports to Manager 1 and has 4 Direct Reports

Manager 3 reports to Manager 2 and has 1 Direct Report

The result would be:

Manager 1 has 3 levels

Manager 2 has 2 levels

Manager 3 has 1 level

Any idea on how to calculate that? I have a list that has the employee reporting hierarchy. I had tried to add a level calc, but cases where there are multiple people reporting to the same manager (siblings) is causing a problem.

Thanks!

Accepted answers

DavidSmith

@OSUBlakester

1. What you'll first need to do is check for each manager for each level. You will need a line item for each level to the maximum that you know you might have

2. Then add a line item (Count) with a '1' formula

3. Add another line item (Level Count), and check for each level for the maximum number of levels using the MAX aggregation method. You need the MAX function to eliminate -Infinity in the results

Hope this helps

David

rob_marshall

@OSUBlakester ,

So David is correct, just missed one additional MAX, the one for the bottome level.

All comments

kavinkumar

HI @OSUBlakester ,

Can you please give some mock up or screen shot of your hierarchy. It would be really helpful to understand the problem.

What does this mean? Manager 2 has 2 levels. Eventhough Manager 2 level has 4 records in it, those are assigned to only 2 parents(Manager 1 items)? If yes, then I have a solution!

Can you please elaborate?

Thanks,

Kavin.

OSUBlakester

Here's a mock up of what I am talking about. I'm ultimately trying to quantify the number of LAYERS (not employees) below a manager.

DavidSmith

@OSUBlakester

1. What you'll first need to do is check for each manager for each level. You will need a line item for each level to the maximum that you know you might have

2. Then add a line item (Count) with a '1' formula

3. Add another line item (Level Count), and check for each level for the maximum number of levels using the MAX aggregation method. You need the MAX function to eliminate -Infinity in the results

Hope this helps

David

rob_marshall

@OSUBlakester ,

So David is correct, just missed one additional MAX, the one for the bottome level.

OSUBlakester

Thanks for the quick responses. I'm getting an error message "Datatype mapping used for aggregation does not match any dimension of the result"

Here's what I did:

Added Count line item, Number format, formula is 1

Added Max Level line item, Number format

Added this formula to the Max Level line item:

Max(Count[MAX:Manager Name],0)

I've tried multiple variations of the datatypes and the formula. The Manager Name is a Line Item that contains the direct Manager. It is a List datatype, using a list that contains a manager hierarchy. Not sure what is causing the error

OSUBlakester

Nevermind, I see that I need to add the count as a list property to the Managers list.

THANKS!

rob_marshall

@OSUBlakester ,

No list properties are needed and shouldn't be used. Everything you need is in the above module. So the Employee's List is really just a "flat" hierarchy where there are no levels since you are defining who the people roll up to. The Report to?, Manager L3, Manager L2, are list formated line items with the Employee Flat as the list. The count is a hardcoded "1", and the summary for the final is just a sum.

Hope this helps,

Rob

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…