1 2 Previous Next 17 Replies Latest reply on Jan 25, 2017 2:15 PM by Joe Oppelt

# Need help/advice on Span of Control

I'm looking to come up with a number of direct and indirect employees a manager supervises, to develop a granular span of control number.  My data has everything required to calculate these numbers, but for whatever reason I cannot get the number to work properly.  I even found a similar topic within community, located hereSpan of Control -Tableau , but I could not replicate that formula properly.

Anyone have any experience with calculating span of control in Tableau?  If so, any thoughts on how you'd even visualize such a thing?

• ###### 1. Re: Need help/advice on Span of Control

Hi Ryan - not sure this is what you are looking for but see below - I'm on 10.1.3 and I don't think you can open a file

I think you were there I just added a countd on the Employee name field and then added the Direct/Indirect measure to columns

Does this help?

Jim

1 of 1 people found this helpful
• ###### 2. Re: Need help/advice on Span of Control

What tableau version is your workbook?  When I open it on 10.1, tableau upgrades it.  If I make changes in that, you will not be able to see them.

I would need to know more about your data here.  I'm assuming Curtis Reusser is top of the heap.  If a guy is in [Reports to level 2], do I assume he will never show up in the level 3 list of people?

if you do a calc like this:

{ fixed [reports to level 1] : countd([reports to level 2]) + countd([reports to level 3]) + ... }

then you will get the sum of all people who report to the level 1 guy (assuming there is no duplications of people among the various levels.)

And if you to the same for each level (adding up the countd() of the underlying levels) you will get the sum of reportees to each guy in there.

Is that what you are trying to achieve?

1 of 1 people found this helpful
• ###### 3. Re: Need help/advice on Span of Control

Sorry realized I should of put in totals/sub-totals - see below

1 of 1 people found this helpful
• ###### 4. Re: Need help/advice on Span of Control

Oh, haha that direct/indirect field has nothing to do with the reports to structure.  Its a labor type (direct means you work on a factory floor).  Sorry for the confusion.

1 of 1 people found this helpful
• ###### 5. Re: Need help/advice on Span of Control

What I'm looking for is a true direct and indirect number.  So for Curtis his direct and indirect number would obviously equal the total number of records.

But, lets say we have a VP named Ryan at level 3 , has 3 direct reports.  One of Ryan's 3 directs, Bob, has 2 direct reports themselves.  I want to be able to to show a total span of 5 for VP Ryan; 3 directs and 2 indirects (Bob's employees).

Make sense?

1 of 1 people found this helpful
• ###### 6. Re: Need help/advice on Span of Control

Give your Tableau version.  I have some things I want to try out.

1 of 1 people found this helpful
• ###### 7. Re: Need help/advice on Span of Control

See attached.  I did direct and indirect for level 1.  You'll need to do the same for each of the levels.

Notice that I culled out [reports to level 2] = "0".  That value is the guy himself, so you don't want to count that.

1 of 1 people found this helpful
• ###### 8. Re: Need help/advice on Span of Control

I added level 2 in this one.  Notice that I don't do the calcs if there is a zero in the current level.

1 of 1 people found this helpful
• ###### 9. Re: Need help/advice on Span of Control

So how would I do this for manager name?  Or even employee name?  Because I think the way the data will be digested will be by the 'company' or 'manager' level.

Reports to Hierarchy is simply an ingredient of that.

• ###### 10. Re: Need help/advice on Span of Control

I'm looking at this but I'm going to have to pick this up tomorrow.

Can you do me a favor?  Can you create this same data source, but instead of putting "0" for [Reports to level 2] for all the level 2 guys, put their name in there instead?  I can make certain things work for Curtis Reusser because he is the level 1 guy, and his name is also in the level 1 field.  But poor old Al Yost doesn't seem to behave for me.  It's a stab, but I think it will work.

You can re-upload the workbook with both data sources in it.  Then I can flip back and forth if needed.

And we don't really need 13000 rows.  Just get Curtis, and the chunk of organization under the first 4 guys in the level 2 column.  (If it's easier just to leave the whole 13000 in there, it's OK.  I was just suggesting that to pare down the size of things a bit.)

• ###### 11. Re: Need help/advice on Span of Control

I dont follow what you're specifically asking for on the '0's?  If there's a 0 in that column thats okay, that just means its the last level.

• ###### 12. Re: Need help/advice on Span of Control

Al's direct and indirect report counts get onto the rows of all his direct reports.  Curtis gets his direct and indirect numbers on his employee row because he is listed in the level 1 names.  If Al were listed on level 2, he would get his direct and indirect numbers on his row, just like Ashkay Hemani gets Al's numbers.

• ###### 13. Re: Need help/advice on Span of Control

Sorry, I'm still not following at all.  What name would I put in reports to level 2 then?  Just assume its 'Al'?

• ###### 14. Re: Need help/advice on Span of Control

For Al Yost, replace his level 2 0-value with his name.  For Balasubramani, replace his level 2 0-value with his name.  Etc for all level 2.  And for level 3, for Brian Olsen, replace his 0-value with his name.  Etc, for all level 3.  And etc for all levels.

All the employees directly under a manager get that manager's DIRECT and EXEMPT values in the calcs I can write for this, but the manager himself does not, because he doesn't exist there when the calc is computing these values.  We can ignore the condition of a guy reporting to himself in the data when counting things, but the guy has to exist in the universe of that direct-report calculation to be assigned values on his employee record.  (At least within the logic of the calcs I have written to do what you need.)

1 2 Previous Next