# Calculating Overall Retention Rate

**James Sweeney**Mar 13, 2015 5:28 AM

Hello,

I hope someone can help me with this. I have a report that calculates the percentage of users that return on a particular day, relative to their install date. The underlying data looks like this:

User ID | Install Date | Play Date | Days Since Install |
---|---|---|---|

ABC123 | 01-Mar-15 | 01-Mar-15 | 0 |

ABC123 | 01-Mar-15 | 02-Mar-15 | 1 |

ABC123 | 01-Mar-15 | 05-Mar-15 | 4 |

GHJ123 | 08-Mar-15 | 08-Mar-15 | 0 |

I can calculate the retention rate by Install Date by Days Since Install easily enough in Tableau (please see attached workbook). The challenge I am having is that when calculating the overall retention rate on day x since install, as the standard Tableau calculation gives me the wrong answer. Let me illustrate with an example:

- Today is 13
^{th}March 2015 - The most recent install date for which I could calculate the day 7 retention rate is the 5
^{th}March. In other words, I know the proportion of users that installed on 5^{th}March that came back 7 days later (12^{th}March, which was yesterday). The rate is 9.3% (13 out of 140 users) as you can see in the workbook. - Now, when determining the overall day 7 retention rate I need to calculate the (total number of users that returned on day 7)/(total number of users on day 0)
**where enough time has elapsed for them to have returned**. - This condition is causing the issue, because Tableau is counting all of the users that returned on day 7 (77 users) and dividing that by all day 0 users (2,033) to give a day 7 retention rate of 3.3%.
- The denominator of all day 0 users is wrong, because it includes users that installed after the 5
^{th}March that haven’t had time to come back on day 7 (as today is 13^{th}March). So the overall day 7 retention rate should in fact be the number of users that returned on day 7 (77 users) divided by all day 0 users that installed between 1^{st}and 5^{th}March (or put another way, up to today-8 days) which is 741. The overall day 7 retention rate should therefore be (77 / 741) = 10.4%.

I’ve illustrated this in the attached spreadsheet in case that is unclear.

Can anyone please explain to me how to perform this calculation in the attached Tableau workbook?

Many thanks

James