0

Shareable rolling average formula

I am trying to create a calculation for the 5-day moving average.
I know there is a built-in rolling average function where I can select how many days back I want to average using a parameter.

However, I want to use this formula as a basis for another calculation, and I have no way to save it or make it shareable.

The calculation I want to perform is the difference between the answer rate on a given day vs. the 5-day moving average answer rate. This will help me determine whether the answer rate on a specific day is higher or lower than the rolling average.

For example, in the first row of the table, I would like to create the following calculation:
8.0% - 7.3% = 0.7%

 The issue is that I cannot simply create a subtraction calculation within constructed functions.
From what I understand, I need to use Formulate to create the rolling average formula first and then calculate the difference.

I tried to do with no success. Could you explain how to create the calculation I need?

Thanks!

5 replies

null
    • Customer Solutions Architect
    • Moshe_Yossef
    • 2 wk ago
    • Reported - view

    Hi 

    The way to do it is to create a formulate that calculates an average over a range for dates form the 4 elements before the current member  and current element.

    here is how:

    In the formulate type Average(,)

    Add a range list before the comma, and a measure after the comma:

    In the range, the first element is the date, Blue FX, lag(4):

     

    and on the second member choose current member:

    and in the measure select the desired member.

    Do notice, as far as efficiency goes, this is far less efficient.
     

    • Shir_BenGal
    • 2 wk ago
    • Reported - view

    Hi Moshe,

    Thank you for your assistance!

    I built the formula as you suggested.

    However, my results are not identical to those of the rolling average constructed function.

    Please take a look as this example, showing the answer rate for a particular phone number:

      Answer rate 5 days rolling average - constructed formula 5 days rolling average - my formula
    2025-02-27 8.398% 8.303% 9.331%
    2025-02-28 6.966% 8.858% 8.858%
    2025-03-03 13.324% 9.497% 9.563%
    2025-03-04 8.394% 9.230% 9.562%
    2025-03-05 4.918% 8.400% 8.879%

     

    In this example, in the last row, the right calculation is 8.400%, but my formula returned 8.879%.

    I think the reason is that the constructed formula takes into account the 5 last NON EMPTY cells, while the suggested formula also takes into account empty cells. In this example, in the dates 2025-03-01 and 2025-03-02, this particular phone number didn't have answer rate since it had no calls. However, the formula does take these null values into account, and basically returns the average of 2025-03-01 until 2025-03-05.

     

    I need a way to calculate the 5 days rolling average only for non-empty dates. Is there a way I can do that in Formulate?

    Maybe to define the range list in a different way?

     

    Thank you!

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 wk ago
      • Reported - view

       

      Try extending the range list, and then putting it in nonempty clause, and finish with a tail.

      Tail(

             Nonempty(

                   Range(10 dates)

                               ),

                5)

       

      I think this will likely also exist for days without data, you can add an if statement to remove them

    • Global Architect
    • Mark_Oldfield.1
    • 2 wk ago
    • Reported - view

    I've made an example based upon my colleague Moshe's idea which you can find here on our 'Explore' server. The example is based upon our Training Models (2023) IMDB database and model.

    You can sign up to our Explore environment here Sign up to Explore

     

    Link to the example on explore HOW TO: n Day Rolling Average - non empty

     

    I've made the example a bit more interactive by adding a few enhancements.

    If you take a look at the 'discover' , you'll notice it now includes two parameters: one lets you choose the Product to calculate the average for, and the other lets you set the number of days for the average to be displayed.

    Discover "Last n days average - Example Products"

     

    Lets walkthrough each part that underpins this discovery and the formulate 'n Days Rolling Avg' .

     

    "Last n Days" - List (Formulate)

    To create the list, we're using three building blocks. The first one is a date filter on the 'Cal date' calendar. It pulls in all the dates between January 1st, 2021, and the 'Current Member' date, which should look familiar from our earlier example.

    Next, we're using a 'Non Empty' node to refine that date range. Basically, this node checks the 'Sales' measure for the selected 'Product' and removes any dates where there were no sales. This gives us a list of dates where the product actually sold.

    And to finish off, we're adding a 'Last' filter. This step grabs the most recent 'N' days from our list. We're using the global parameter '# of days' to tell it exactly how many days to include.

    "n Days Rolling Avg" - Formulate

    The rolling average calculation on the discovery looks as follows 

    For this part, I decided to use an 'Aggregate' element, which works with the list we built. The 'Aggregate' looks at the 'Sales' values for the chosen 'Product', adds them all up, and then divides that total by the '# of days' parameter we have. It's just another way to get to the same result!

    To add a nice finishing touch, I've set up a dynamic caption for the formula. This means the title of the formula will automatically change to show the exact number of days you've selected in the '# of days' parameter.

    I hope this example helps to explain the approach.

     

    thanks

    Mark.

      • Shir_BenGal
      • 2 wk ago
      • Reported - view

       

      Thanks for your detailed answer, Mark!
      It was really helpful.

Login to reply

Content aside

  • Status Answered
  • 2 wk agoLast active
  • 5Replies
  • 40Views
  • 3 Following