Get weekly avg and top 1 topic

I would like to add another 2 column which are

  • avgerage of downloads in last 5 weeks
  • top 1 downloaded topic ( to see what is driving the download changes)

I did WoW percentage using by
periodOverPeriodPercentDifference(distinct_count({execution_id}), {deck_generation_date})*100

this syntax and making deck_generation_date in weekly in the field well.

How do I get last # week’s average? feels like I have to use running avg?


Hello @qsqqs, welcome to the QuickSight community! In regards to getting the average number of downloads in the last 5 weeks, the best way to get that value, while ensuring it is dynamic with new data, you would want to create 2 parameters.

I’ll include screenshots of how you want want to set those up below.


Then create a calculated field that checks if the date exists between those 2 parameters, if so return the value you want to average, else null.
ifelse(${StartWeek} <= {Date} AND ${EndWeek} >= {Date}, {Downloads}, NULL)

Then you can try an average aggregation from the field well and see if the numbers make sense. If there is still a problem, you can create another calculated field like this:
ifelse(${StartWeek} <= {Date} AND ${EndWeek} >= {Date}, 1, 0)

Then get your average by doing this: sum({Calc1})/sum({Calc2})

Let me know if that helps! Oh and a side note, you do not need to multiply your periodOverPeriodPercentDifference calculation value by 100. Instead you can set that field to display as a Percent value in the field well.

Hi Dylan, I ended up using windowAvg

	     [{deck_generation_date} DESC],

My following question would be I’d like to exclude and get average from
for example if today is 10/2/2023
I’d like to have average of 9/3 ~9/30 week )5week from the last week).
however, in window avg function, I can’t have -1 start index.

index that I might want would be 5 rows’ average below current row.
Do you know how to get this?

Thank you!

Hello @qsqqs, my apologies for the delayed response! If the windowAvg function is working for you as expected, you should be able to use the parameter based filters like I suggested above.

If you still need help with this question or if you have a new QuickSight question, please create a new question topic so you can be at the top of the priority list for a response from one of our QuickSight Experts. Thank you!