2 Replies Latest reply on Aug 7, 2012 12:07 PM by Tracy Rodgers

    Moving average with null values

    Grzegorz Mikulski



      Can anyone help me with moving averages calculation?


      I have Budget same in every month (B), but actuals (A) only appear in certain months:

      Nowy obraz.jpg


      As you see in month 2 and 3 actuals were null (no entry in database). My first question - is there a way to display A for months 2&3 with value "0"?


      Second thing - when I calculate moving average - for month 2&3 nothing is calculated.

      For month 4 Tableu only calculates: (month 1 + month 4 )/2  =  (3,3+2,8)/2 = 3,05

      My idea for calculation would be : (month 1 +  month 2 + month 3 + month 4) / 4  =  (3,3 + 0 + 0 + 2,8) / 4 = 1,525


      I tried custom formula like  RUNNING_SUM(SUM([FTE])) / RUNNING_max(max([Month])) - this calculates correct amount in month 4, but in month 2&3 nothing is displayed...


      Is there any way to acheive this?


      Thanks for support,


        • 1. Re: Moving average with null values
          Tracy Rodgers

          Hi Grzegorz,


          The reason that A is not appearing in months 2 and 3 is because there is no data for them, it is not null data. One way to get it to appear in the view is to either add it directly to the data or create a lookup table with each month and PeriodCd and join it to the original table.


          In terms of the calculation that wants to be created, it should be similar to the following:


          [Running sum FTE]/total(countd(Month))


          To get the countd function to work, you will need to create and use an extract.




          Hope this helps!



          • 2. Re: Moving average with null values
            Grzegorz Mikulski



            Adding data is not possible - my example was quite trivial, but in fact my database is more complex. In rows I have different Domains, and under these domains different entities.


            I tried custom SQL but it was calculating for ages.


            Finally I managed to crack this myself without lookup tables. For anyone who is interested - I used a custom calculation (computed using MONTH):


                 RUNNING_SUM(SUM([FTE])) / [Running month number]


            where Running month number is calculated as:


                 IF ATTR([Month]) = LOOKUP(ATTR([Month]),-1) THEN






            This means even if there are no FTE's in given month, the formula will still divide the running total by running month number.


            Running average.png