Summarize data by month for date range

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • In Progress
I have an application that lists projects and the effort required for each. I would like to create a chart that summarizes the total of all effort by month using the start and end dates of the projects. It sounds simple, but I can't figure out how to do it. Only the timeline report seems able to show multiple projects across months using start and finish dates. I can't use that to summarize the total effort by month, though. Is there some way to do this that I am missing?

Thanks!
Photo of kpmcdona

kpmcdona

  • 112 Points 100 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Yes, you are correct.  Sounds easy but it is not.  One approach would be to have a set up a bunch of of fields on the Project record such as
[Hours Reqd Current Month]
[Hours Reqd Current Month +1]
[Hours Reqd Current Month +2]
[Hours Reqd Current Month +3]

Then you can have a report which shows the hours required each month relative to the current month.  Not sure if that can be turned into a chart, but it will give you a decent report.
Photo of kpmcdona

kpmcdona

  • 112 Points 100 badge 2x thumb
Yep. Ironically it was much easier for me when the PMs were forced to estimate effort by month instead of for the entire project in one go.  Easier for me, but apparently too hard for them. I am now trying to figure out if I can automate creating monthly effort related records using QB Actions or Automations - and then add/edit/delete effort records if the start or end date changes. Ugh. My head hurts. ;)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Yes, that was the other option that I did not go into.  on every project creation or edit to the dates, you would need to wipe out the child records and then create say up to 12 future records (I assume you only want to look forward) for the Project effort in each month.  I suppose because we don't have an IF statement, you would need to create all 12 records and some will have zero effort if they are outside the project end date. 

 You could then have the automation carry on and delete any children with zero effort in a month. 
Photo of kpmcdona

kpmcdona

  • 112 Points 100 badge 2x thumb
Clever! I was starting to think this would require coding a webhook or two, and my code-fu is weak. This way sounds sort of brute force, but I think it would work.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Right, so that would be an Automation to fire and then do those steps.

Delete current children
Create new children 
Delete zero hours children.
(Edited)