I think this is a challenging problem, maybe not for some of you pros, but at least it will be interesting and I am using it in real life to show the power of Tableau and sway the company I work for to buy more licenses. Please help as I have been trying to break through, for hours.

This fake data set is relatively simple. I have 5 leads that are assigned to four employees. Each employee has, over time, acted upon these leads, and have dispositioned them as {Created, Dialed, Reached, Closed}. The part that gets a little complicated is this: each lead can be dispositioned multiple times, for a long time after the lead is created. Each disposition has an age which is simply the days between the disposition date and the date the lead was created. The dispositions don't necessarily have to go in order but the furthest within the time frame selected is what matters.

I'm trying to make a dynamic report with a age slider that shows the number of leads actioned by each employee and only each lead's furthest disposition with less than or equal to the chosen age.

Here is some sample data.

So the problem statement is: I want to know where every single lead is cumulatively ranked at any given time between the date the lead is created and the age slider. For example, if age = 11 (or less), the Lead 117 would return Dialed from the Cumulative Disposition column. If the slider were moved to 13, Lead 117 would return Reached because the furthest disposition was reached on 9/14.

But he result must look like a pivot, and is for reviewing the Employees' closing ability, so it needs to have Disposition type across the top, Employee ID on down the left, and a count of distinct leads. Another pivot could show a vertical stacked bar chart with the number of distinct leads at their cumulative disposition, with created month going across the bottom.

Please give it an honest try. I'm just not yet able to get it even close. Sincerely appreciate the assistance.

Ruben