I have changed your calc into
COUNTD([User Id]) / total(COUNTD([User Id]))
total(COUNTD([User Id])) will give overall total users.
Thanks & Regards
Thanks for your reply but total(COUNTD([User Id])) will give me overall totals all the time, I want only those users who could participate, for eg. I added two more users in data set, in that one registered yesterday and another one registered today, user who registered yesterday can't participate for day2 to day 5 divisor because for that users these days are future day same for user who registered today can't participate for day 1 to day 5. So result should be
Day 0 = 22/22
Day 1 = 14/21
Day 2 = 11/20
Day 3 = 08/20
Day 4 = 04/20
Day 5 = 02/20
test_2.twbx 28.9 KB
Can you help understand how is 14/21 being calculated? I understand 14, but don't understand where the 21 come from?
Look at the sample table attached, it basically how many user come on a particular day and divide by how many could have come on that day. N/A means that the difference b/w user's signup date and current date is less than that day. For eg look at User7 and you see that he signup on today (May 22) and therefore can't have returned on his Day 1 (Day 0 + 1 day) since that is the future whereas User5 could have returned on his Day1 because his signup was 2 days ago.
Hope this answers your question.
Please find my solution attached.
Below is the sample output
the calculation is complicated, but have a look first and if you have question, please do come back and ask, I will explain more details.
test_2_v10.5.twbx 44.9 KB
Really thanks for the work, I should have been more clear about my problem. I also was able to get the retention like you showed but my problem is when I applied filters. Lets say from the dataset above that 60% of signups are male, spread randomly among a signup days and 40% are female and I want to compare the retention of male v/s female e.g when I apply a Gender filter the results are strange. I get 157% retention for male on Day0. This is the problem that is really crushing my head.
I would appreciate any help you can give.
First of all thank you very much for your help, it works perfectly when I added just a gender filter but we are not there yet. When I added Reg Date filter too I got the results shown in the chart. If you look at Day1 you see it is shows 11 users as the possible returnees when in reality all 12 users could have return on that day so the divisor should be 12 not 11 (because for all filtered users difference b/w reg date and today's date > 1).
I am also trying to figure this out but if you could take a look at this I would appreciate it.
Again thanks for your help.