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

Rolling Distinct Count

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.

• 1. Re: Rolling Distinct Count

Hi

sorry

you will need to look at each day and get the IDs for the day with   {fixed (day level of date),(full vis id)  : (

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Rolling Distinct Count

Thank you for your reply. Unfortunately, WINDOW_COUNT didn't work. Any other suggestions?

• 3. Re: Rolling Distinct Count

Check this KB

hope this helps

BR,

NB

• 4. Re: Rolling Distinct Count

First I created a count customer using an LOD:

1)  { FIXED [Full Visitor Id], [BQdate(M-D-Y)] : MIN(IF [BQdate(M-D-Y)] = { EXCLUDE [BQdate(M-D-Y)] : MIN([BQdate(M-D-Y)])} THEN 1 ELSE 0 END) }

2) Using that calculation I created RUNNING_SUM(SUM([LOD Count]))

Hope this helps. If does, please mark it as helpful and correct to close the thread and so other users can refer to it. Thanks!

Best,

Diego

• 5. Re: Rolling Distinct Count

Thank you very much Naveen. This solved my problem.