5 Replies Latest reply on Jun 25, 2019 5:58 AM by Ahmad Rasheed

    Rolling Distinct Count

    Ahmad Rasheed

      Hi,

      I have a small data set and I am trying to calculate rolling distinct number of 'Full Visitor Id' over a period of 7 days.

      My data set is as below:

      Full Visitor Id                        BQdate(M-D-Y)

      -----------------------------------------------------------

      1234                                   3/1/2019

      1235                                    3/2/2019

      1236                                    3/3/2019

      1234                                    3/4/2019

      1238                                    3/5/2019

      1239                                    3/6/2019

      1240                                    3/7/2019

      1241                                    3/8/2019

      1242                                    3/9/2019

      1242                                    3/10/2019

      1235                                    3/11/2019

      1245                                    3/12/2019

      1246                                    3/13/2019

      1234                                    3/14/2019

      1235                                    3/15/2019

      1238                                    3/16/2019

      1234                                    3/17/2019

      1235                                    3/18/2019

      1238                                    3/19/2019

      1247                                    3/20/2019

      1248                                    3/21/2019

      For every date on the X Axis, I am trying to count the unique users for the rolling 7-day period (day minus 7). For example on the date '3/21/2019' I am trying to find the distinct number of users until '3/15/2019'. On the date '3/20/2019' I am trying to find the distinct number of users until '3/14/2019'.

      Initially I put together a calculation as below:

      WINDOW_SUM(COUNTD([Full Visitor Id]), -6, 0)

      Unfortunately, it calculates distinct visitors id for every day individually and then sum the results for 7 days. Hence there is a chance of duplication. I tried to do it using a LOD but it still doesn't work.

      My modified calculation is as below:

      WINDOW_SUM( COUNTD(if [BQdate] = {FIXED [Full Visitor Id]:MAX([BQdate])}then [Full Visitor Id] end), -6, 0)

      Any help will be much appreciated.