build a project timeline

icechunk

Say we have data that looks like this

• Project, start date, end date, Total hours
• Task A, Jan 2023, June 2023, 50
• Task B, Feb 2023, July 2023, 100

If we assume a smooth work schedule where work to be done in a month for a project is total hrs/number of months. How can i lay this out, so we know how many hours of work needs to be done in a given month?

In the above example, it would be 150 hrs/6 months, so 30 hrs a month of work to be done.

GrantSmith

The following beast mode / formula would get you the number of hours per month of work that would need to be done

`Total hours` / PERIOD_DIFF(DATE_FORMAT(`end date`, '%Y%m'), DATE_FORMAT(`start date`, '%Y%m'))

icechunk

thanks. But how do i lay this on a chart? So it shows total hours per month.

GrantSmith

You can't just do this with the way your data is currently structured. Domo required each month to have a record for it to be displayed. You'll need to feed your data into an ETL and have a record for each month with the monthly amount.

First use the formula above in an Add Formula tile to calculate the amount per month. In that tile also add another column called Grouping Column with a value of 1. Then you'll need to add the Domo Dimensions connector's Calendar dataset as an input. Filter this dataset where d = 1 so you only have one record per month. Use another Add Formula tile to add another Grouping Column field with a value of 1.

I'm assuming your date fields are actually a date. If it's a string you'll need to convert them to a date with some formulas formula (one for start date and one for end date, just update the field name):

STR_TO_DATE(`start date, '%b %Y')

Once you have those two datasets prepared you can join both of them together on your Join Column, then use a filter tile to filter using a condition like

`dt` BETWEEN `start date` and `end date`

You can then use the dt field in your table to display the number of hours needed per month

icechunk

yes i saw your response on another post on using calendar dataset. It works well.