2 Replies Latest reply on Feb 18, 2013 10:42 AM by Ruben Rosado

Challenging Problem

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.

 Create Date Lead Disposition Date Days Aged Employee Disposition Rank Cume Disp Cume Disp Rank 1/1/2012 297 1/1/2012 0 112523 Created 0 Created 0 1/1/2012 297 1/2/2012 1 112523 Reached 2 Reached 2 1/1/2012 297 1/3/2012 6 112523 Dialed 1 Reached 2 1/1/2012 297 1/4/2012 8 112523 Reached 2 Reached 2 7/21/2012 366 7/21/2012 0 116852 Created 0 Created 0 7/21/2012 366 7/27/2012 6 116852 Dialed 1 Dialed 1 7/21/2012 366 7/28/2012 7 116852 Closed 3 Closed 3 8/29/2012 211 8/29/2012 0 112406 Created 0 Created 0 8/29/2012 211 8/30/2012 1 112406 Reached 2 Reached 2 8/29/2012 211 9/29/2012 31 112406 Dialed 1 Reached 2 9/5/2012 117 9/5/2012 0 115924 Created 0 Created 0 9/5/2012 117 9/6/2012 1 115924 Dialed 1 Dialed 1 9/5/2012 117 9/14/2012 12 115924 Reached 2 Reached 2 9/5/2012 117 9/27/2012 22 115924 Closed 3 Closed 3 12/10/2012 129 12/10/2012 0 116852 Created 0 Created 0 12/10/2012 129 12/11/2012 1 116852 Dialed 1 Dialed 1 12/10/2012 129 12/12/2012 2 116852 Reached 2 Reached 2 12/10/2012 129 12/17/2012 7 116852 Dialed 1 Reached 2 12/10/2012 129 1/4/2013 25 116852 Closed 3 Closed 3

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

• 1. Re: Challenging Problem

Ruben,

Take a look at the attached workbook and see if it is what you are looking for.  It uses the Last() table calculation as a filter.  There is commentary in the workbook to describe the different steps of solving the problem.

I'd be happy to answer any questions you might have!

Joshua

1 of 1 people found this helpful
• 2. Re: Challenging Problem

Hi Joshua,

This is GREAT!!! Thanks for the explanation in the captions, it's very easy to follow. I'm very impressed. I've never used Table Calculations.

So there are a couple of things here that are needed for completion. First, I modified so that it would show Cume Disp instead of Disposition, because if the lead was dispositioned as "Closed" on day 2 for example, and later was "Reached" or "Dialed", the "Closed" disposition would trump all other dispositions on the report, as long as the filter is >=2 days.

The part I'm having difficulty understanding is when the filter goes to the max (31), the pivot shows a value of 1, employee 116852 shows a count of 1 for closed, instead of 2. But everything else seems to work as expected...

Thanks for your help here man!