Hello,
I am trying to create the moving average of the last 14 periods
the calculation i am using is:
windowAvg(sum({earning}),[{year_period} ASC],14,0)
In my visual (a line graph) i need to present only the last 14 periods. However if i will add a filter of the 14 periods the calculation is breaking and the values are not correct.
earning=numeric data type
period =string data type
so what i tried to do is to create a calculation with if else but it was presenting an error message about aggregated and non aggregated values.
I tried to use the sumif(), or to add a sum() in the front of the if else, then was popping up the nesting aggregation error.
I tried to push this calculation in the dataset (QuickSight dataset not in the back end) again the same error.
the closest I’ ve been to the result i want is : in lines graph properties, to add 14 Number of data points to show but to present the last 14 periods i need to Descending my period, thus though present the data from the latest period (this is not something that i want)
I need exactly like that but sorting out ASC the 202401 be in the from and the last period to be the 202501.
Thank you for your help 
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 QuickSight, you can follow these steps:
- 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.
- 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).
- 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
Hello @Xclipse, thank you so much for the quick response.
I tried to use the calculation but i couldn’t. The “rank_over” doesn’t exist as a function in QuickSight. the only functions that exist are “Rank” and “PercentageRank”
also the year_period is a string data type so i can’t do calculations with it
If it is helps, i have a field that calculate the periods ago, we are calling it “periods_ago”.
while periods_ago=0 is current and periods_ago=1 is the previous one and etc
I tried to use your thought and change a lit bit the calculation but it didn’t work, probably i am doing something wrong while i am trying to use the periods_ago
I will keep try because this is something very important for us.
again thank you for your help and the guidance
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:
- 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.
- 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.
- 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)
Hello @Xclipse,
If i will take a filter of 14 i am losing the moving avg of 14 periods that i want to display.
i made a quick example in an excel
as you can see in the example the light blue cells are the periods and the right values i want to display.
For example the last period 202502 is calculated: =AVERAGE(P3:AD3)
the previous Period 202501: =AVERAGE(O3:AC3) and etc.
If i will take a filter of periods_ago<14 the i am losing the avg of the period i want to present
Thank you for your help
Hi @Sofia-Bourika, how about using windowAvg function or runningAvg function. And here is another discussion that maybe helpful.
Please let us know if this approach works.
Hello @Xclipse,
if you see the first comment i am using windowAvg, and is working as expected but as the picture presents
i need to use the “Number of data points to show:14”
and sorting out in the opposite side (descending) from what i need to present the data
as you can see in the picture 202501 is the first in the X axis and 202401 is the last one. I need to be the 202401 to be the first point in my X axis and 202501 the last.
(i am using a filter for periods ago between 1 and 28, that’s why my windowAVG is working as it is expecting)
Thank you for your help @Xclipse
@Sofia-Bourika, please can you try to provide an example in the QuickSight Arena, so I can try out different methods. In the meantime, you can try using off-visual metric sorting.
Hello,
i just created the Moving AVG in QuickSight Arena
Moving AVG
Much appreciated your help,
Thank you
Hi @Sofia-Bourika, sorry for the delay, can you try this, is the calculation correct? I filtered 1 thru 14. Moving AVG - Analysis Filter on Line Graph 1 thru 14
Please tag @Xclipse, so I can get notified when you respond.
Hello @Xclipse,
Don’t worry that’s fine for your delay
So if you filter from 1 to 14, the calculation is not correct.
check from these 2 tables, the correct values are in the table “Last 28 Periods” while the last 14 periods are wrong. I updated the Analysis with the table
Moving-AVG
Thank you for your Support
1 Like
Hi @Sofia-Bourika, thanks for the detailed explanation – so, it’s a visualization adjustment using “off-visual field” for sorting, then adjusting the X-axis “Number of data points to show,” set to 14 or 15. As shown below.
Period Year Sorting Calculation
ifelse({year_period}=202401,100,ifelse({year_period}=202402,200,ifelse({year_period}=3,300,ifelse({year_period}=4,300,ifelse({year_period}=5,600,1000)))))
I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!
3 Likes
YES! I can confirm this feature the “off-visual field” is the solution of my problem!!!
thank you so much @Xclipse
here is a link with the calculation and everything of my solution in case someone would like to have a look
Moving AVG Final
2 Likes
