2 Replies Latest reply on Jul 16, 2015 10:16 AM by Larry Hill

    Last Status by Date and Counting Statuses

    Dave Shaw

      I'm relatively new to Tableau and have run into some struggles with what I think is a fairly basic Excel data set. The table includes information on a number of legal cases and lists the date that each case changed status. An example of the data is below:

       

        

      Case No.

      Date

      Status

      Case #1 6/14/2007Operation
      Case #1 10/11/2007Operation Closed
      Case #1 10/12/2007Arraignment
      Case #1

      12/09/2007

      Arraignment Closed
      Case #112/10/2007Pre-Trial
      Case #2 6/12/2014Arraignment
      Case #2 7/7/2014Arraignment Closed
      Case #2 7/8/2014Pre-Trial
      Case #2 8/3/2014Pre-Trial Closed
      Case #2 9/11/2013Prosecution

       

      I'm trying to analyze the data in a couple of different ways:

       

      1. First, I'm trying to create a table that lists the current (most recent) status for each case. For example, in the data above, the most recent status for Case #2 should be prosecution. I've tried some different approaches with a calculated field using LAST () == 0 and then filtering the FALSE results, but some of the statuses are incorrect. I think my table calcs are incorrect but I am struggle to figure out where.
      2. Second, I'm trying to display the number of cases in each different status for a given time period. For example, I'd like to be able to show that in Q2 2011, there were 10 cases in the Pre-Trial phase, but in Q3, that dropped to seven cases in Pre-Trial because three of them moved into the Prosecution phase. One of the issues that I've run into is that not all cases follow the same progression, so most cases may go from Operation -> Arraignment -> Prosecution -> Defense, etc, but others might go from Operation -> Arraignment -> Judgement if there is a plea bargain.

       

      It seems like this should be simple to do, but I keep running into the same problems and would appreciate any help.


      Thanks.