Distinct sum over rolling period

Mon, 11 Apr 2016 09:56:52 GMT

Dear community

I'd be very greatful to receive some help, figuring out the following (for me) very challenging problem:

In broadcasting, we use the unit "Reach" to describe the share of viewers who have seen (at least one minute) of a program during the last X days. So imagine a table with the following columns, each record representing one minute of one viewer watching my TV channel:

DateTime (date & hour & minute)
ViewerID (let's assume that we have a universe of 1000 viewers, each with a unique ID)
Weight (not all viewers have the same value. The sum(weight) for all viewers in the universe are 100%. The weights are slightly adjusted for all viewers every day to achieve this.)
ProgramName (e.g. "Star trek")

During the last week (X=7), let's say Star trek aired a couple of times. But what is the "Reach"? What function can I use to get Qlik to tell me what share of viewers who have seen at least one minute of Star Trek during the last 7 days? (NB, since the weight is slightly changing every day, I need to use the average weight for each viewer over the 7 days.)

I'm looking forward to seeing some bright ideas on this! Thanks a lot!

Tor

NB: I cannot use set analysis to filter out the last 7 days, because I need to show the development of Reach over a periode, hence the function needs to refer to the last 7 days (rolling).

Re: Distinct sum over rolling period

marcus_sommer — Mon, 11 Apr 2016 14:48:00 GMT

Have a look here: Calculating rolling n-period totals, averages or other aggregations

- Marcus

Re: Distinct sum over rolling period

Tue, 19 Apr 2016 15:45:59 GMT

Thanks, Marcus! With a bit of struggle, I finally solved this with AsOf-tables, as suggested by the article.

topic Re: Distinct sum over rolling period in App Development

Distinct sum over rolling period

Re: Distinct sum over rolling period

Re: Distinct sum over rolling period