Plot project stages on a timeline in Tableau: Dumbbell Chart
In this blog we will talk about plotting various stages of a project on a timeline. In my opinion, the chart most appropriate to represent this is a dumbbell chart.
However, dumbbell charts refer to two data points, where else in our scenario more than two points are represented on the timeline. Thus, there is a need for a slightly different approach. Let’s see how.
Data Prep
I’ve created a sample file with one column that lists the project name with its start date labelled as ‘Stage0’. Followed by 7 more columns named ‘Stage1’, ‘Stage2’ and so on. Both correct and incorrect data was entered: blank, nulls, wrong order of dates, and even huge gaps in between various stages for few of the projects.
For data prep, Alteryx was used. While tools used in Alteryx workflow are for another blog, the process followed are documented here:
a. For each stage of the project, introduce a new column that states whether that stage has a null value or not. For example, for column ‘Stage0’ insert/ create column ‘Stage0-TAON’ (to appear or not) with value ’N’ if no date is available and ‘Y’ if date is available. Similarly, for ‘Stage1’, ‘Stage1-TAON’ will have value ’N’ or ‘Y’, depending on date availability. Hence, at the end of this step we will have 8 new columns. Explanation as to why columns with TAON are being introduced will be provided as we proceed.
b. Then barring ‘Stage0’ and ‘Stage7’, set the value of that stage — if empty or null — as that of the previous stage value. For example, for Project 10, ‘Stage3’, ‘Stage5’ and ‘Stage6’ dates are not available. So, we populate the date for ‘Stage3’, ‘Stage5’ and ‘Stage6’ with that of ‘Stage2’, ‘Stage4’, and ‘Stage5’ respectively. We have to take this step to maintain continuity in the line while plotting the chart.
When blank cells are being populated with that of the previously available date, then there has to be a way to identify the data cells where data was manipulated and where data was genuinely available. Hence, to distinguish the difference, step ‘a’ was introduced.
c. Observe that, now we have 17 columns. Hence, the next step is to pivot the columns.
i. First, we pivot columns ‘Stage0’ to ‘Stage7’ and name the pivot header column as ‘Stage Number’ and value column as ‘Stage Concluded Date’.
ii. Simultaneously, we will pivot columns ‘Stage0-TAON’ to ‘Stage7-TAON’ and rename the pivot header columns as ‘TAON Flag’ and value column as ‘TAON Flag Value’.
d. Now, introduce one more column named ‘Name’ which has the following formula:
LEFT([TAON Flag], 6) // Basically it carries the string for example, Stage0 or Stage1
e. Now perform inner join on these 2 pivoted tables we created (steps ‘i’ and ‘ii’ in point c), on the basis of ‘Project Name’ and where ‘Stage Number’ is equal to ‘Name’ field.
f. Select the relevant columns which will be ‘Project Name’, ‘Stage Number’, ‘Stage Concluded Date’, ‘TAON Flag’, and ‘TAON Flag Value’.
In nutshell, pivot the date columns in the original file and find a way to identify and mark the cells where we are populating data from the previous field of the same project to be able to demarcate them later. This understanding is needed so that corresponding functions and steps in other technologies like SQL is the only step left to implement.
Plotting on Tableau
Now that we have data in the format we can use, let’s proceed with plotting the same on Tableau.
a. Set the ‘TAON Flag Value’ as Y and set it as a context filter on the sheet.
b. Place ‘Stage Concluded Date’ on Columns and set it as Exact Date and continuous
c. Place ‘Project Name’ on Rows.
d. Set ‘Stage Concluded Date’ as Line in Marks card.
e. Drag and drop ‘Stage Number’ on the Path card and on the Color card. This step is optional as the line can be set to a lighter shade of any one color as well.
f. On the Columns bar, create a Dual axis for the field ‘Stage Concluded Date’ (set as exact date and continuous) and Synchronize them.
g. For the second ‘Stage Conclude Date’, select Shapes in the Marks card. Select the shape as a filled circle. Here, ensure that ‘Stage Number’ is there on the Color card too.
h. Create a calculated field named ‘Label for Stage’ with the following text:
This will be used to label the circle so that it’s easy to identify the stage as well as discrepancies in the data. For example, if the conclusion date for ‘Stage5’ falls before the conclusion of ‘Stage1’ then this data error can’t be missed.
i. Create another calculated field named ‘For tooltip with proper formatting’ with the following text and select format the date should appear in the tooltip:
[Stage Conclude Date]
j. Place the above two calculated fields in label and tooltip respectively.
k. For reference, find the link to the workbook here. Download option is available.
Conclusion
One can definitely plot the original file without prepping the data. The output will be as shown in the image below: with circles appearing to hang in space and broken lines wherever the dates are missing.
Personally, a light grey thin line connecting all points is a better take.
While plotting the project phase timelines, it is vital that the distance between two project phases is clear and any data issues, for example wrong order of date, should be visible without breaking the graph. For the stakeholder involved, it becomes imperative to quickly distinguish cases where width or gap between different phases of a project is immediately visible and these projects can be raised for further investigation.
Until next time.. Sayonara
