3 Replies Latest reply on Jan 30, 2013 1:00 PM by Jonathan Drummey

    Table Calculation: Comparing Latest Status to Status on Reference Date

    mohen leo

      Hi!

       

      I have a list of status updates for items with dates when the status was updated.

       

      Itemstatus updatestatus
      A9/27/2012on-schedule
      A9/30/2012on-schedule
      A10/6/2012delayed
      A10/8/2012delayed
      B9/29/2012on-schedule
      B10/7/2012delayed
      B10/8/2012on-schedule
      C9/28/2012on-schedule
      C10/1/2012delayed
      C10/6/2012on-schedule
      C10/8/2012delayed

       

      I need to compare the latest, most up-to-date status for each item with the status of the same item at a particular reference date and isolate only those items which are "delayed" now but were "on-schedule" on the reference date (however, if an item changed status in between but is now back to the same status it was on the reference date, it should not be included).

       

      For example with the attached data (sample.xlsx), let's say the reference date is 10/5/2012:

      - Item A should be listed in the result as it was "on-schedule" on 10/5/2012 but is now "delayed".

      - Item B should not be listed as it was "on-schedule" on 10/5/2012 and is "on-schedule" now (even though it was briefly "delayed" in between)

      - Item C should not be listed as it was already "delayed" on 10/5/2012 and is still "delayed" (even though it was briefly "on-schedule" in between)

       

      My main problem in the table calculation is trying to determine the status for each item on the reference date (even though the last update for some items may have been several days before the reference date).

       

      Any advice?

       

      Thanks!

       

      mohen

        • 1. Re: Table Calculation: Comparing Latest Status to Status on Reference Date
          Jonathan Drummey

          Hi Mohen,

           

          Hopefully the attached meets your requirements. I created a parameter and three calculated fields:

           

          - Reference Date - the parameter

          - day of status update - to prevent Tableau from doing overly aggressive padding of the domain

          - Status on Reference Date - this uses a LOOKUP() table calculation to help identify the status on the reference date, and deal with the fact not every day has a status

          - Delayed - returns 1 when the Status on Reference Date = on schedule and ATTR(status) = delayed, and Null for everything else. This allows you to set up a view that is filtered for Delayed = 1.

           

          Jonathan

          • 2. Re: Table Calculation: Comparing Latest Status to Status on Reference Date
            mohen leo

            Hi!

             

            I have a follow-up problem, also related to capturing changing statuses as they are on certain dates throughout a project's history:

             

            I’ve simplified the problem and data set for the example (sample data excel file is attached).

             

            I have a record of tasks and status changes on the tasks, with the dates the status changes were made.

             

            task

            status change date

            status

            A070

            1/4/2012

            inactive

            A040

            1/7/2012

            A

            A060

            1/9/2012

            A

            A060

            1/10/2012

            inactive

            A080

            1/13/2012

            B

            A060

            1/13/2012

            inactive

            A080

            1/14/2012

            inactive

            A040

            1/17/2012

            inactive

            A030

            1/20/2012

            B

            ...

            (and so on)

             

            What I need is an area chart that shows the status history of the active tasks.

             

            With the date on the horizontal axis, I would like an area chart that for each date shows the total number of tasks with status A and total number of tasks with status B at that time, while excluding inactive tasks.

             

            (rough hand-drawn sketch, not matching provided data)

             

            taskhistorysketch.jpg

             

            Tasks can go back and forth between statuses over time, become inactive and active again, and there aren’t status entries on every date for every task. Also, new tasks are added over time that didn’t exist from the start.

             

            For every date, I need to get the most recent status of every task up to that date, exclude inactive tasks, and chart the number of status A tasks and status B tasks.

             

            I can't figure out the table calculation that will do that.

             

            Any help would be appreciated.

             

             

            Thanks!

            • 3. Re: Table Calculation: Comparing Latest Status to Status on Reference Date
              Jonathan Drummey

              Hi,

               

              I saw that you'd already posted this in a separate thread, in the future please only post a question once, that way the folks trying to answer your question won't end up duplicating efforts.

               

              Jonathan