Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rolling averages

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?

1 Solution

Accepted Solutions
sunny_talwar

Try this:

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

Capture.PNG

View solution in original post

9 Replies
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

Not applicable
Author

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


Capture.PNG

Not applicable
Author

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!

Not applicable
Author

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

before.JPG

After selection

after.JPG

sunny_talwar

Try this:

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

Capture.PNG

Not applicable
Author

Thanks, that did the trick!

Not applicable
Author

Hi Sunny,

can you please help me with this?

Thanks in advance!

Stravan

Average and rolling