build a project timeline

icechunk
icechunk Member
edited February 2023 in Charting

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.

Answers

  • 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'))
    


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • thanks. But how do i lay this on a chart? So it shows total hours per month.

  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • yes i saw your response on another post on using calendar dataset. It works well.