3 Replies Latest reply on Oct 24, 2018 4:33 PM by Zhouyi Zhang

# Succession planning - how to consolidate the unconsolidate-able?

Dear all,

I am struggling now for several hours with a problem that likely everyone in HR has in one or the other way: succession planning.

A simplified scenario is that you have positions (aka "jobs"), incumbents on these positions and you think ahead who may take over this job once the current incumbent is gone. Unfortunately, there might be several candidates per position and there might be several potential positions for one succession candidate.

To make things a bit more tangible, below a simplified example:

- we do have 6 candidates

- we differentiate their "readiness" in two levels 1 & 2

- we have positions in two Divisions A & B

- Candidate 5 may be a successor for a position in Division A as well as in Division B

- Candidate 6 may be a successor for two different positions in Division A for which she/he has different readiness levels

--> see table top left of screenshot

Lets make an important assumption: I want to report on the succession candidate pipeline PER DIVISION.

That implies that every candidate for a position in a Division counts - regardless that the same person may be also candidate in another Division (example: candidate 5 shall be counted in Division A and in Division B)

On the other hand, we do not want to count a single candidate several times within any given Division when reporting on how many candidates we have in this Division.

So I try to get to a report matching "C" in this example: 4 candidates in Division A (so not double-counting candidate 6) and 3 candidates in Division B.

This is obviously different than just counting all IDs (8) and different to a count distinct (6).

Hope you get the idea. And, yes, this is a bit odd compared to how Tableau is built and typically used for.

Any idea how to realize an output like "C"?

I am thinking in a kind of 2-step-approach:

1) create a subset of the whole per Division

2) somehow tell Tableau to eliminate IDs with multiple appearances (pick the first, delete the other - just like Excel's "Delete duplicates" feature)

3) then do the report straight forward, based on this "per Division dataset" while still having it in one report for all Divisions

But I do not want to hard-code the Divisions in Sets as Divisions will change and some other things make it more complex in reality. But if you can guide me the way for this simple example, I'll hopefully make it then through the real world :-)

Jürgen

P. S. I am using Tableau 2018.3 Desktop

• ###### 1. Re: Succession planning - how to consolidate the unconsolidate-able?

Hi, Jurgen

Below is the screenshot of the calculations and the result for your reference.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Succession planning - how to consolidate the unconsolidate-able?

Xie xie ZZ!

That helps a lot and I hopefully manage my way through this now from here :-) As being relatively new to Tableau, I was yet never in touch with "FIXED" function.

Great that this forum still works like those 20 yrs back at the earlier days of the Internet!

• ###### 3. Re: Succession planning - how to consolidate the unconsolidate-able?

You are welcome

I am glad it helps. You will find this forum is really great place to communicate and learn Tableau quickly.

ZZ