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

# Table Calculation: Comparing Latest Status to Status on Reference Date

Hi!

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

 Item status update status A 9/27/2012 on-schedule A 9/30/2012 on-schedule A 10/6/2012 delayed A 10/8/2012 delayed B 9/29/2012 on-schedule B 10/7/2012 delayed B 10/8/2012 on-schedule C 9/28/2012 on-schedule C 10/1/2012 delayed C 10/6/2012 on-schedule C 10/8/2012 delayed

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).

Thanks!

mohen

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

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

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)

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

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