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.
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.
status change date
(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.
test_data_tableau.xlsx 12.4 KB
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.