Discussions

Expand all | Collapse all

How many projects are active in a given month?

  • 1.  How many projects are active in a given month?

    Posted 11-08-2018 23:24
    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.


  • 2.  RE: How many projects are active in a given month?

    Posted 11-09-2018 01:27
    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.


  • 3.  RE: How many projects are active in a given month?

    Posted 11-09-2018 20:06
    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


  • 4.  RE: How many projects are active in a given month?

    Posted 11-11-2018 16:03
    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.


  • 5.  RE: How many projects are active in a given month?

    Posted 11-13-2018 19:46
    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)



  • 6.  RE: How many projects are active in a given month?

    Posted 11-13-2018 21:34
    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.




  • 7.  RE: How many projects are active in a given month?

    Posted 11-19-2018 20:38
    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



  • 8.  RE: How many projects are active in a given month?

    Posted 11-19-2018 23:16
    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.