I'd like to see how much work is scheduled per month based on total price and start and end dates.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I've got a report that shows the duration of projects on a timeline. Our projects can last from 3 days to 5 months. I'd also like to see if we've scheduled enough/too much work for each month, so I want to have a report that shows how much we've scheduled each month based on the total of portions of various projects. I was thinking maybe I could take the project total/duration to get a daily total, but I can't figure out how to make a report to total a certain month's scheduled work total. I'm thinking I might have to create a new child table, but I'm open to any suggestions.

Photo of Chris

Chris

  • 0 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,330 Points 50k badge 2x thumb
I can tell you the approach, but I don't have the time to work out the exact formulas. You can contact me off line via the information in my profile if you need the formulas worked out.

I suggest that you only likely care about say the next 6 months plus the balance of the current month.

Building on your thought about using the daily work content in each project I would make a formula to calculate Project Hours Current month plus 1. i.e. Next Month.

You would set up formula variables for the first of the Current month plus 1, and the last day of the current month plus 1.

Then calculate how many days that project was active for between those two dates, multiplied by the daily average work content.

Once you get that formula working, it's a trivial matter to copy it and adjust it to give Project Hours Current month plus 2 i.e., month after Next.

Repeat for about 6 months out and if you like the current month remaining and then you just make a simple table report with a list of projects with hours in any if those 7 fields, and have the project name and those 7 fields as columns.

As an aside, you probably want to calculate workdays excluding weekends to get your daily work content.
Photo of Chris

Chris

  • 0 Points
Super awesome advice! I got that to work and it's coming out great. Thank you very much! Now I'm trying to get the field label to show up as a month depending on the current date. Right now I've got the field labeled "Month $ -1" for last month's portion and "Month $ 0" for this month and "Month $ 1" for next month and so on. But I'd really like to make them say "December", "January", and "February", but change relative to the current date. Is it possible to use formulas in the field label?
Photo of Chris

Chris

  • 0 Points
And just for the record, here's the formula I came up with, but I'm sure yours would have been much more elegant.
[Estimated Total Price]/WeekdaySub([Calculated Fab at 90% Date],[Calculated Fab Start Date])*
If(Min(WeekdaySub(LastDayOfMonth(AdjustMonth(Today(),-1)),FirstDayOfMonth(AdjustMonth(Today(),-1))),
WeekdaySub([Calculated Fab at 90% Date],[Calculated Fab Start Date]),
WeekdaySub(LastDayOfMonth(AdjustMonth(Today(),-1)),[Calculated Fab Start Date]),
WeekdaySub([Calculated Fab at 90% Date],FirstDayOfMonth(AdjustMonth(Today(),-1))))>0,
Min(WeekdaySub(LastDayOfMonth(AdjustMonth(Today(),-1)),FirstDayOfMonth(AdjustMonth(Today(),-1))),
WeekdaySub([Calculated Fab at 90% Date],[Calculated Fab Start Date]),
WeekdaySub(LastDayOfMonth(AdjustMonth(Today(),-1)),[Calculated Fab Start Date]),
WeekdaySub([Calculated Fab at 90% Date],FirstDayOfMonth(AdjustMonth(Today(),-1)))),0)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,330 Points 50k badge 2x thumb
Wow, that formula hurts my head, but if it works, that is great.  I try to break down complicated formuals into descrete formula variables to do any sub calculations and then assemble them in the final cleaner formula. http://www.quickbase.com/user-assistance/Default.html#formula_variables.html

Floating field labels by formula cannot be done on reports really.  BUT, once I was in a similar situation and what I did was to make a dummy project and contrived to have it list 1st on the report.  Say it was record #123

Then you could preserve the formuals you have now - they are already complicated, but make a new formula field like

IF([Record ID#]<>123, toText(Round([My complicated field above])), [a formula field to calculate the month name in bold])