Creating an "unassigned" row for gantt chart visualization

mhouston

I'm working on a gantt chart visualization for resource assignments, and trying to add an "unassigned" bar to the chart for any time periods when a resource is not assigned to a project.

My dataset is generally structured as resource name, assignment name, start date, end date:

I've pulled in the domo dimensions calendar data set, and have been able to explode out these dates so that I have 1 row per resource, per assignment, per date. I also used this to create rows where Assignment Name = 'UNASSIGNED' if the resource has no assignment for a given date. I then tried grouping by resource name and date so that I had a column assignment type equal to either 'ASSIGNED' or 'UNASSIGNED' for each date, and that's where I got stuck:

I'm not sure how to take this and get the start and end date for each section of 'UNASSIGNED' to append to the original format so I can put it on the gantt chart. I thought I could do a group by but then realized that using that and the min/max functions would give me the very first date someone is unassigned and the very last date - but if there are multiple sections where they are unassigned (as above it would be 2/15/2022-2/28/2022 and then 5/1/2022 and on), it would not be correct.

Has anyone done something similar to this? Any tips or ideas? Is there an easier way to do this that I'm just missing?

MarkSnodgrass

I would suggest keeping your data in your original format and then building out your unassigned dates by using the rank and window tile and a formula tile. I did it by doing the following:

1. Connect your original dataset to a rank and window tile and then create a column called Last End Date and choose the Lag function using the End Date column with an offset of 1. Order by the start ascending and partition on the resource name
2. Add a formula tile next and create 3 columns that look like this

This will result in an output like this:

You can then filter to unassigned equal 1 and overwrite your assignment name column to unassigned and then append to your original dataset using the unassigned start and end dates as your start and end dates.

Hope that makes sense.

MarkSnodgrass

I would suggest keeping your data in your original format and then building out your unassigned dates by using the rank and window tile and a formula tile. I did it by doing the following:

1. Connect your original dataset to a rank and window tile and then create a column called Last End Date and choose the Lag function using the End Date column with an offset of 1. Order by the start ascending and partition on the resource name
2. Add a formula tile next and create 3 columns that look like this

This will result in an output like this:

You can then filter to unassigned equal 1 and overwrite your assignment name column to unassigned and then append to your original dataset using the unassigned start and end dates as your start and end dates.

Hope that makes sense.

mhouston

@MarkSnodgrass thank you!! This was really helpful (and much simpler than what I was thinking) - I was able to take this and run with this to get what I need.

Writing up the full solution for anyone who runs into something similar:

I anonymized a subset of the data and ended up finding some edge cases I hadn't considered - 1) resources with no assignment and 2) unassigned days before the first assignment for the resource and after the last assignment for a resource and 3) overlapping resource assignments i.e. Assignment A and B have the same start date but different end dates:

I applied the logic you gave me (and added a sort on end date in the rank and window to handle overlapping assignments like curly's that both start 10/13) which filled in all the gaps:

I was then able to extend the logic to handle these red bars:

The case where nobody is assigned is a simple formula for start date is null and end date is null.

For the days before the first start date:

1. Rank and window tile - create a column last_start_date that is the lag of start date, ordered on start and end date
2. Formula tile:

For the days after the last end date:

1. Rank and window tile - create a column next_end_date that is the lead of start date, ordered on start and end date
2. Formula tile:

Then append all of these to the original dataset and boom, magic.

Now I just have to switch in the real dataset to this logic :)

GrantSmith

@mhouston

Thanks for the detailed write up of your solution!