In your second LOD for Denominator you don/'t need Rolling 28 day because you need data for entire period. You were calculating Same data in second LOD by distributing it per day in LOD.
If it Helps, Pl mark it Helpful and CORRECT to Close Thread
I’ll take a look tomorrow, but I don’t think that will work, because then it appears (from your image) to be dividing by the entire reporting period which is several years, not the previous rolling 28 days COUNTD. Can you attach a workbook sample so I can see what you did? Thank you!
I was reading your response on my phone; checking out the workbook now. Need to run some tests but this might be it! If so I'll mark this as correct. Thank you!
Yeah, unfortunately this answer does not work. For example, using the sample data, if we were to calculate it by hand for 12/30/18 there are:
Daily: 4 unique purchasers
Rolling 28 (12/2/18 – 12/30/18): 140 unique purchasers
This equates to 0.02857142857 stickiness
The formula you used for Rolling 28 comes up with 793 (which is the total unique count for the sheet, not just the range of 12/2/18 – 12/30/18) and equates to a stickiness of 0.005044136192 (which is reflected in your image.)
So, unfortunately this is not the fix either.
Does anyone know how to calculate a rolling 28 day COUNTD when the data you're working with is measured in individual transactions (can't be lumped into daily buckets because then you lose the ability to calculate rolling 28 uniques).
There is an 'old school' SQL-based approach
to counting Uniques in a Moving Window.
The main idea is to use a Range Join on Date
with the Calendar table (at the proper granularity).
The approach has been discussed in the Forums recently:
Please find the attached as an example.
rolling_dau_mau_sample_v10.5_YF.twbx 294.1 KB
Hi Yuriy —
Thank you for the reply and example. I can't make sense of what is happening (I've never done Range Joins before) and I'm not sure where/what the "Date" field represents in this data. In my data it would never be different than the Order Date, so I can't make sense of where it is coming from or how it works. Sorry!
The more research I've done, the more I think the solution you provided is the one that is going to work, but if you can just ELI5 this for me it would be incredibly helpful. Particularly the bit about joining to a calendar table and the different dates that are utilized. Thank you!