1 Reply Latest reply on Apr 21, 2017 7:23 AM by Jim Dehner

    Visualize Schedule with Recurrences

    Connor Sims

      Hi,

       

      I have a table with about 10,000 unique jobplans. I want to count & visualize the number of jobs scheduled in Fiscal Year 2018 (7/1/17-7/1/18).

       

      In my packaged workbook, I show 3,909 job plans who's next occurrence (NextDateCalc) happens sometime during FY18.

       

      My problem is this: of these 3,909 jobplans, some have different frequencies (once a year, once per 3 months, once a month), so if I wanted to count how many total (non-unique) jobs will occur in FY18, I need to factor this in.

       

      How can I calculate the total # of jobs scheduled as opposed to simply the # of unique jobplans that are scheduled at least once this year?

       

      Any feedback is much appreciated.

       

      Thanks,

      Connor

        • 1. Re: Visualize Schedule with Recurrences
          Jim Dehner

          Hi Connor

           

          I think this is a solution - there are some questions I have about how you want to address Nulls for next start date and 0 for "Frequency"

           

          but see below - the way the chart is read is for each PMnum that has a Next Start Date within a month (Blue Bar)  the Orange bar is the total number of times those pmnums will be executed before 12/31/2018

           

          The Key Calculations are

          Number of months between next start date and 12/31/2018 > DATEDIFF('month',[Nextdatecalc],makedate(2018,12,31))

          and the number of times the pmnum is done before 12/13/2018> IF [Months next start - end of year]>0 then Int([Months next start - end of year]/[Frequency]) else 0 end

           

          Let me know if this does work

           

           

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.