Rank records based on highest average of previous 10 months

robindc — Sat, 16 Nov 2024 19:17:53 GMT

Hi!

My data currently has the following structure:

TOP_MONTH - WEBPAGE_TOP - MONTH_SUM_VISITS

201911 - 560 - 25

201911 - 561 - 37

202001 - 560 - 27

202001 - 561 - 29

Aggr({<TOP_MONTH=>}
RangeAVG(Above(Sum(MONTH_SUM_VISITS), 1, 10))
, WEBPAGE_TOP,TOP_MONTH)

Now, I would like to take 5 webpage id's which have the highest last 10-month average to this day, so only on records for month: 202001.

i've tried using the Rank function, with aggregation on Webpage, but it doesn't work.

Rank(
Aggr({<TOP_MONTH=202001>}
RangeAVG(Above(Sum(MONTH_SUM_VISITS), 1, 10))
, WEBPAGE_TOP))

Does anyone have an idea how I can fix this?

Thanks!

topic Rank records based on highest average of previous 10 months in App Development