Moving AVG

Question & Answer
, ,
4

Hi @Sofia-Bourika, thank you for your detailed feedback and for providing information about your data fields. Given that year_period is a string and sorry, I confused rank_over with another function (if all this does not work, we may have to take a different approach with LAC-W, LAC-A) and these examples. But for now, let’s adjust the approach using the periods_ago field you mentioned. Here’s how you can calculate and display the moving average for the last 14 periods:

  1. Modify Your Calculation: Since periods_ago provides a numeric representation of periods relative to the current period, we can directly use this for filtering and calculating the moving average. Here’s the adjusted calculation:
ifelse(
    periods_ago < 14,
    avgOver(sum({earning}), [{periods_ago} ASC], PRE_AGG),
    NULL
)

This calculation checks if periods_ago is less than 14 (which means it includes the current period and the 13 previous periods), and calculates the moving average for these periods. Otherwise, it returns NULL.

  1. Apply a Filter: To ensure that your graph displays only the last 14 periods, set a filter on the periods_ago field to include only values less than 14:
periods_ago < 14

This filter ensures that your visual only includes the current period and the 13 periods before it.

  1. Configure the Line Graph: In the line graph properties, ensure that you set it to display the appropriate number of data points. You might not need to explicitly set this if your filter is correctly applied, as it will automatically restrict the view to the last 14 periods.

By implementing these changes, your QuickSight visual should now accurately reflect the moving average of the last 14 periods using your periods_ago field. If you encounter any further issues or have additional questions, feel free to reach out. Your success with these visuals is important, and we are here to help!

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)