1 Reply Latest reply on Dec 26, 2018 2:30 AM by Yuriy Fal

    Moving average not picking up parameter selection

    Airin Alamsjah



      I have created a parameter (Remove Outlier) to remove data where Daily Average Duration is greater than the selected number.

      Then I would like the 'Running Avg' worksheet to show me the latest result (running average as at the last day – I have done this by using lookup function (last) by each service type.

      But, the result does not change with the parameter selection and even without the parameter selection, the result seems to only work for service type F (refer to more comments below).


      Here is my formula:

      Running Average (Test) =

      IF [Daily Average Duration] <= [Remove Outlier] then

      RUNNING_SUM(SUM([Duration Weekday]))/RUNNING_SUM(COUNT([Transaction Number]))



      Note that I don’t use the moving average formula, because it averages the daily average duration. Whereas I want it to average the running sum.


      Then I used the lookup formula to get the latest number.

      Max Running Average (Test)

      LOOKUP(([Running Average (Test)]),LAST())


      It also seems like it is working for Service Type F (6.10) but not for the other service type (for example A shows 9.92 in the Running Avg sheet, but 9.64 in Moving Avg Parameter sheet.


      Please help!!

      Thank you

        • 1. Re: Moving average not picking up parameter selection
          Yuriy Fal

          Hi Airin,


          Are you trying to 'remove' (not take into account)

          any [Create Date] & [Service Type] Dim values combination

          that result in [Daily Average Duration] aggregate value

          more than the Parameter threshold?


          If yes, then both [Created Date] and [Service Type] Dimensions

          should be included -- either on a view (as on the Sheet 7 workout)

          or (and) in the calculations (as on the Sheet 7).


          Besides, since both the Numerator and the Denominator are additive,

          the last RUNNING_SUM() value is equal to the WINDOW_SUM() one,

          hence the results.


          Please find the attached.

          Hope it could help.