9 Replies Latest reply: Jan 19, 2017 11:34 AM by Khabat Stravan RSS

    Rolling averages

    Magnus Buseth

      Hi,

       

      Short version: How can I make the first graph show the same total as the second graph?

       

      I'm trying to do calculations based on a rolling average number of subscribers. I have set up an example to better explain my problem.

      In my example, I have data on the number of subscribers to a streaming service, and how many hours of each genre is watched each month. I want to combine this data to calculate:

       

      1. The average number of hours watched per subscriber

      2. The average numbers of hours watched per subscriber, divided by genre

       

      My data only has the subscriber count at the end of each month, so I take the average of the incoming subscriber count and outgoing subscriber count for each month.For this I use rangeavg() and above():

      rangeavg(above(sum(Subscribers),0,2))

       

      So 1. is solved by this formula:

      Sum(Hours)/rangeavg(above(sum(Subscribers),0,2))

      avg1.JPG

      For 2. however, the forumla does not work anymore, probably because the above() function stops working correctly. This is what I get:

       

      avg2.JPG

       

      The total does not add up. I would like to divide the hours watched by the total (average) number of subscriptions, to see each genres contribution to the average hours watched.

       

      I have tried different variations of TOTAL, but can't get it to work. I have also read this very good documentation on rolling averages, but have not been able to solve this.

       

      Do I need to calculate the rolling average number of subscribers in my load script instead? If so, how would I do that?