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 :-)
Thanks a lot in advance,
P. S. I am using Tableau 2018.3 Desktop