Moving AVG

Q&A
, ,
2

Hi @Sofia-Bourika, to achieve a moving average of the last 14 periods while presenting only the last 14 periods in your line graph in Amazon Quick Sight, you can follow these steps:

  1. Modify your Calculation: Instead of using the windowAvg function directly, you can create a custom calculated field that calculates the moving average only for the last 14 periods. Here’s how you can do it:
ifelse(
    rank_over(sum({year_period}),[{year_period} ASC], PRE_FILTER) > (count_over(1,[{year_period} ASC], PRE_FILTER) - 14),
    windowAvg(sum({earning}),[{year_period} ASC],14,0),
    NULL
)

This calculation checks if the current period is within the last 14 periods. If it is, it calculates the moving average; otherwise, it returns NULL.

  1. Apply a Filter:
  • Apply a filter to your visual to display only the periods where the moving average is calculated. You can use a calculated field or a filter directly on the {year_period} field.
  • For example, you can use a filter condition like: rank_over(sum({year_period}),[{year_period} ASC], PRE_FILTER) > (count_over(1,[{year_period} ASC], PRE_FILTER) - 14).
  1. Set Data Points to Show:
  • In the line graph properties, set the “Number of data points to show” to 14. This ensures that only the last 14 periods are displayed in your visual.

By following these steps, you should be able to achieve your desired outcome: a line graph displaying the moving average of the last 14 periods, with only the last 14 periods shown on the graph.

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)

1 Like