I would like to add another 2 column which are
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.
StartWeek:
EndWeek:
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
windowAvg
(
distinct_count({execution_id}),
[{deck_generation_date} DESC],
0,
4
)
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!