Rolling averages

[#1]

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))

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

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?

[sunny_talwar]

It might help to know the expected output (numeric value) for each Genre in the stacked bar chart for first three to four months. Right now, I understand that the total value needs to match, but I am still trying to understand what the breakup by Genre is going to be

[#3]

Yes, I understand. I have attached an Excel-file that recreates the expected data and graph. Thank you!

[sunny_talwar]

Try this:

Sum(Hours)/Aggr(nodistinct RangeAvg(Above(Sum(Subscribers),0,2)), (Period, (Numeric)))

[#5]

Thank you, this works perfectly! Very impressive

I understand how Aggr() works, but would you care to explain why you need the nodistinct and (Period, (Numeric)?

[sunny_talwar]

I will let you first explore them on your own, but if you still have issues, I will be more than happy to explain

1) NoDistinct -> Qlik Tips: No nodistinct

2) (Period, (Numeric)) -> The sortable Aggr function is finally here!

[#7]

Thank you, I will read up on those.

However, I discovered one issue with the formula. If I select a single month, the number changes.

EDIT: It seems that when a month is excluded from the selection, this makes the calculation for the average subscribers of the next month fail.

For example:

Before selection

After selection

[sunny_talwar · Accepted solution]

Try this:

Sum(Hours)/Aggr(nodistinct RangeAvg(Above(Sum({<Period>}Subscribers),0,2)), (Period, (Numeric)))

[#9]

Thanks, that did the trick!

[#10]

Hi Sunny,

can you please help me with this?

Thanks in advance!

Stravan

Average and rolling