How many projects are active in a given month?

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • In Progress
I have about 50 or so active projects that span over several months, some are wrapping up, others are starting up. I've added start and end dates, but I cant seem to figure out how to calculate how many projects will be active in a given month. I want to be able to forecast how many active projects I will have in each month moving forward.
Photo of Matthew DiMarco

Matthew DiMarco

  • 100 Points 100 badge 2x thumb

Posted 1 month ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
The reason that you are struggling is that when you make a summary report the underlaying record (a project) can only appear in one "cell" if the summary report.

A solution is to create a series of fields such as

[Project is Active Current Month]
[Project is Active Current Month +1]
[Project is Active Current Month +2] etc

 you can go out say 12 months.

Then you would just run a simple table report to get your totals.

The formula would be like this for the field

[Project is Active Current Month +2]


 (not tested)


var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2)); 
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));


[Project Start Date]<= $ActiveMonthEnd 
and
[Project End Date] >=  $ActiveMonthStart

// adjust the 2 for each successive copy of the field.
Photo of Matthew DiMarco

Matthew DiMarco

  • 100 Points 100 badge 2x thumb
Thanks for your reply. I basically understand what your describing here as a solution, but I think it may be a bit advanced for what I can do. I'm not sure of all the field types or how many fields this would take to create. Is there a simpler way? 

- Matthew
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
You will need 1 field for month ahead you want to look. But  once you get one working, then you can copy the field and just make a minor change to it.

The field type would be formula Numeric, and here is a corrected formula

IF(
[Project Start Date]<= $ActiveMonthEnd 
and
[Project End Date] >=  $ActiveMonthStart,1,0) 

Give it a try and post back if you get stuck anywhere.
Photo of Matthew DiMarco

Matthew DiMarco

  • 100 Points 100 badge 2x thumb
I made a field for November. The formula seems to result in "1" no matter of the start date. Can you review the formula below and let me know what I'm missing? Also, what number do I adjust to be the next month's field? Does it reset in Jan?

var date ActiveMonthStart = FirstDayOfMonth(AdjustMonth(Today(),2)); 
var date ActiveMonthEnd = FirstDayOfMonth(AdjustMonth(Today(),2));

If(
[Est Installation Start Date]<= $ActiveMonthEnd 
and
[Est Completion Date] >=  $ActiveMonthStart,1,0)

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I just did a test and it seemed to work for me.  Please tell me what dates you are using to test, which you feel are not working.

The formula example was for the month "2" months ahead.  Today is November, hence 2 months ahead is January.  So the formula should return a 1 if the project is active in January.

The "2" is the part of the formula that you would change to a 0 for the current month, a 1 for the current month plus 1 (December if today is November), then copy and change to a change to a 2 for 2  months ahead or today's month, etc up to say 12 for 12 months ahead of the current month.

So once you get the formula working to your satisfaction, you copy the formula say 12 times to look into the counts for each of the next 12 months.


Photo of Matthew DiMarco

Matthew DiMarco

  • 100 Points 100 badge 2x thumb
Ok, so this is working now! So I guess I'll need to change field names each month as it goes by?

Now, I'm trying to make a report (summary I believe) to show the totals for each month. Any guidance here?

Thanks!
Matthew

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I would not change the field names each month.  I would either rename them to have very short names such as

CM
CM+1
CM+2
CM+3

ie, "current month plus 3"

The Report will just be a Regular report.  If you do want the detail of each project, then it is a list of all project names and then say those 12 or 13 CM columns.

if you need to export to excel for the totals you would need to make a summary report type.

I also suggest making a field to totals up all 13 CM fields called [# Active Months], and then use that as a filter on the reports.  ie [# Active Months] >0.