Understanding Gantt Chart Dates, and Date Filters

PJG · August 2023

I'm struggling with this more than I think I should be, but as always, I probably need an ELI5 version :)

I have a Gantt chart where my Start/End Date is my Project Start/End Date.

My first question is simple; how do I have my Gantt chart start at today, regardless of what the Start date is? I want today to the last end date of all projects shown as the timeline.

The second part adds another wrinkle. At the same time as the above requirement, I don't want to exclude any projects from being displayed that have their entire timeline prior to today. I still want them to appear on the chart.

Are these possible?

Thanks!

GrantSmith · August 2023

For the first part, you can use a beast mode to select the greater of CURRENT_DATE and your task date and use that as your date.

You can update the logic in your beast mode to also check to see if the end date is < the CURRENT_DATE and if it is then just use the start date and end date normally.

CASE WHEN `end_date` <= CURRENT_DATE() THEN
  `start_date`
WHEN `start_date` < CURRENT_DATE() THEN
  CURRENT_DATE()
ELSE
  `start_date`
END

MarkSnodgrass · August 2023

You can use a beast mode to exclude dates prior to today by doing this:

CASE WHEN startdate < CURRENT_DATE() THEN 'Exclude' ELSE 'Include' END

You would drag this beast mode into your filters and filter to Include. You could also choose to use enddate instead, so it wouldn't show all past projects.

However, your 2nd part indicates that you actually want to show them. Either I am not understanding what you want, or it is somewhat contradictory.

In the Chart Properties → General You can click the checkbox Show Current Date. This will give a visual representation of where today is. This might be a good way to give your users a sense of where today is when viewing the chart.

DavidChurchman · August 2023

In addition to Mark's suggestion of turning on the 'Show Current Date' line, you can also calculate your percent complete to always be up to today's date, like:

case
when DATEDIFF(CURRENT_DATE(),Start Date) / DATEDIFF(End Date,Start Date) > 1 then 1
else DATEDIFF(CURRENT_DATE(),Start Date) / DATEDIFF(End Date,Start Date)
end

That would give you something like this, where the stuff in the future is that striped texture:

Or you could reformat the formula to make the past striped.

If you truly want the start of the graph to be the start date and still see past projects, you could have a beastmode for your start date like:

CASE WHEN Start Date >= CURRENT_DATE() THEN
Start Date
WHEN Start Date < CURRENT_DATE() THEN
CURRENT_DATE()
ELSE
Cohort Start Date
END

and another one for your end date like:

CASE WHEN End Date >= CURRENT_DATE() THEN
End Date
else CURRENT_DATE()+10
end

Depending on your date range, you can play with that +10. The idea is all past projects have a little bit sticking out so you can still see them. You can also play with the labels or the percent complete to make it clear these represent past, complete projects.

GrantSmith · August 2023

For the first part, you can use a beast mode to select the greater of CURRENT_DATE and your task date and use that as your date.

You can update the logic in your beast mode to also check to see if the end date is < the CURRENT_DATE and if it is then just use the start date and end date normally.

CASE WHEN `end_date` <= CURRENT_DATE() THEN
  `start_date`
WHEN `start_date` < CURRENT_DATE() THEN
  CURRENT_DATE()
ELSE
  `start_date`
END

MarkSnodgrass · August 2023

You can use a beast mode to exclude dates prior to today by doing this:

CASE WHEN startdate < CURRENT_DATE() THEN 'Exclude' ELSE 'Include' END

You would drag this beast mode into your filters and filter to Include. You could also choose to use enddate instead, so it wouldn't show all past projects.

However, your 2nd part indicates that you actually want to show them. Either I am not understanding what you want, or it is somewhat contradictory.

In the Chart Properties → General You can click the checkbox Show Current Date. This will give a visual representation of where today is. This might be a good way to give your users a sense of where today is when viewing the chart.

DavidChurchman · August 2023

In addition to Mark's suggestion of turning on the 'Show Current Date' line, you can also calculate your percent complete to always be up to today's date, like:

case
when DATEDIFF(CURRENT_DATE(),Start Date) / DATEDIFF(End Date,Start Date) > 1 then 1
else DATEDIFF(CURRENT_DATE(),Start Date) / DATEDIFF(End Date,Start Date)
end

That would give you something like this, where the stuff in the future is that striped texture:

Or you could reformat the formula to make the past striped.

If you truly want the start of the graph to be the start date and still see past projects, you could have a beastmode for your start date like:

CASE WHEN Start Date >= CURRENT_DATE() THEN
Start Date
WHEN Start Date < CURRENT_DATE() THEN
CURRENT_DATE()
ELSE
Cohort Start Date
END

and another one for your end date like:

CASE WHEN End Date >= CURRENT_DATE() THEN
End Date
else CURRENT_DATE()+10
end

Depending on your date range, you can play with that +10. The idea is all past projects have a little bit sticking out so you can still see them. You can also play with the labels or the percent complete to make it clear these represent past, complete projects.

PJG · September 2023

Hi all, thank you so much for taking the time to respond to me. Sorry for the delay in getting back to this topic, but this is not my primary responsibility, and I am always getting pulled in many different directions. For that reason also, an ELI5 is always very much appreciated, as this is not my main area of expertise.

I tried the code from @GrantSmith & @MarkSnodgrass, but I am not getting it to achieve exactly what I am looking for. While it does show the Gantt from today onward, it also filters out projects I still want to see on it. I will try to reword my requirement:

Essentially, I am looking to change the display only, and I don't want it to filter anything out of my Gantt if it meets my other criteria. I simply want the left hand most side of the Gantt to be today. If a project is running from Jan 1st to Dec 31st 2023, I don't want to see the portion of the bar for Jan 1st to Sep 10th. I only want it shown for Sep 11th to Dec 31st. The Gantt should always start from today. When I look at it tomorrow, it should start from Sep 12th, etc.

Perhaps my problem is that I'm putting my ProjectStartDate into the START DATE that Domo asks for? But if not this, then what? I tried putting the Current Date from the Beast Mode in there, but this showed no bars at all.

Thank you

EDIT: Another similar requirement, I only want my Gantt to show 1 year (2024), but again, I don't want to filter anything out; visual only.

GrantSmith · September 2023

That would need to be an idea in the Idea Exchange for Domo to allow the ability to "zoom" to a specific time period in your data without filtering out your data as it's currently not supported.

PJG · September 2023

https://community-forums.domo.com/main/discussion/comment/76479#Comment_76479

Hi @GrantSmith yes, zoom is a good way to describe it. I'm surprised this isn't a feature, as I thought it would be a common requirement. To show a 2024 Portfolio for instance. Thanks for the reply anyhow.

Understanding Gantt Chart Dates, and Date Filters

Best Answers

Answers

Categories