Aggregated divison of rolling sum by cell

Question

I have a dataset with the variables: date, product_id, country, revenues, 30DayActiveUsers

I would like to aggregate the average (of each day within the date filter) of the ratio [ sum revenues of the last 30 days (which is the sum of the revenues for the 30 days up to each “date i”) ] divided by [30DayActiveUsers (which is only one cell in the same row as “date i” because it's already aggregated)].

The final dataset format has the product_id or country in the rows and the indicators,such as this one, in the columns.

I have tried many ways such as

avg{ include Date: window_sum(sum(revenues), -29,0) / avg(or sum)30DayActiveUsers }

but it always throws an error .

Any ideas?

Answer 1

Without having a sample workbook, it's hard to see a possible solution. But what I can say is that Level of Detail calculations cannot include a table calc. The window_sum is creating your error. Perhaps try a different approach.