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

    Challenging Problem

    Ruben Rosado

      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 DateLeadDisposition DateDays AgedEmployeeDispositionRankCume DispCume Disp Rank
      1/1/20122971/1/20120112523Created0Created0
      1/1/20122971/2/20121112523Reached2Reached2
      1/1/20122971/3/20126112523Dialed1Reached2
      1/1/20122971/4/20128112523Reached2Reached2
      7/21/20123667/21/20120116852Created0Created0
      7/21/20123667/27/20126116852Dialed1Dialed1
      7/21/20123667/28/20127116852Closed3Closed3
      8/29/20122118/29/20120112406Created0Created0
      8/29/20122118/30/20121112406Reached2Reached2
      8/29/20122119/29/201231112406Dialed1Reached2
      9/5/20121179/5/20120115924Created0Created0
      9/5/20121179/6/20121115924Dialed1Dialed1
      9/5/20121179/14/201212115924Reached2Reached2
      9/5/20121179/27/201222115924Closed3Closed3
      12/10/201212912/10/20120116852Created0Created0
      12/10/201212912/11/20121116852Dialed1Dialed1
      12/10/201212912/12/20122116852Reached2Reached2
      12/10/201212912/17/20127116852Dialed1Reached2
      12/10/20121291/4/201325116852Closed3Closed3

       

      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
          Joshua Milligan

          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
            Ruben Rosado

            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!