2 Replies Latest reply: Apr 19, 2016 11:45 AM by Tor Kielland RSS

    Distinct sum over rolling period

    Tor Kielland

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