5 Replies Latest reply: Jun 12, 2015 9:45 AM by Stefan Wühl RSS

    forcast

      Hello,

       

      I hope somebody can help me with this. A customer of mine wants to see a rolling avarage (as he calls it) into the future.

       

      An example of his definition of a rolling avarage:

      YearMonthRolling avarage

      2015

      jan=count(TaskID)=3
      2015feb=count(TaskID)=6
      2015mrt=count(TaskID)=6
      2015apr=count(TaskID)=5
      2015may=count(TaskID)=9
      2015jun=count(TaskID)=8
      2015jul=((5+9+8)/3=7.33
      2015aug=((9+8+7.33)/3)=8.11
      2015sept=((8+7.33+8.11)/3)=7.81

      As you see the customer wants that when there aren't any TaskID values the Rolling avarage of the last periode is taken. In this way he can "predict" future task capacity.

       

      I hope this is posible. Thanks in advance!

        • Re: forcast
          Stefan Wühl

          It's probably more easy to calculate in the script, but I assumed you want a chart based solution.

           

          Maybe like attached?

           

          edit: Updated with your values, result:

          YearMonthExp
          37
          2015Jan3
          2015Feb6
          2015Mar6
          2015Apr5
          2015May9
          2015Jun8
          2015Jul7,3333333333333
          2015Aug8,1111111111111
          2015Sep7,8148148148148
            • Re: forcast

              Thank you for your solution! This is most of what i need. The problem is when i select a quarter or a couple of months or 1 year (when i have data from more years) the expression isn't working anymore. How can i still have the rolling avarage over three months in a selection?

               

              Ofcourse the dimensions of the table (or chart) should react to the selection and only show the values of the months, quarters or years i selected. But the calculation within the expression shouldn't react to the selection.

                • Re: forcast
                  Stefan Wühl

                  Selecting Months, like Apr to Sep, doesn't break my table as far as I see. Of course the forecasted months needs to be part of the selection.

                   

                  Could you give a more specific example of how your data looks like and what you want to see when making specific selections?

                    • Re: forcast

                      If you make the selection Apr to Sep it does stay the same. But what if you make the selection May to Sep, the table will be as followed:

                      2015May9
                      2015Jun8
                      2015Jul8.5
                      2015Aug8.5
                      2015Sep8.33

                      And i still want to see the values with the three month rolling avarage and not changing to two or three months as it did in the selection i made.

                        • Re: forcast
                          Stefan Wühl

                          Here is a different approach:

                           

                          Assuming the number of forecasted month is limited to three, we can pre-calculate the forecasted values and assign to three variables we can then use in the chart expression.

                           

                          This makes it selection insensitive (well, at least you can control via the variable expressions which selections you want to be considered and which you don't).