10 Replies Latest reply on Mar 1, 2017 6:20 AM by Jim Dehner

# Member retention calculation - help please

Hi All,

I am a new user of Tableau. Trying to implement a member retention calculator dashboard with the below formula.

Retention Rate = ((CE-CN)/CS)) X 100

CE = number of customers at end of period

CN = number of new customers acquired during period

CS = number of customers at start of period

I am looking to implement this retention rate in Percentage between financial years 1st july 2012 - 30th june 2013, 1st july 2013 - 30th june 2014, 1st july 2014 - 30th june 2015, 1st july 2015-30th june 2016, 1st july 2016- 30th june 2017 on a line graph.

Would greatly appreciate any help with how to put this into tableau.

I am attaching a sample data file.

• ###### 1. Re: Member retention calculation - help please

Hi Sanjay

See the attached T10.1 file

I think this is what you want - couple things to note - you can change the start of the fiscal year by Right clicking the Date (in the Dimension frame) >> Default Properties >> Fiscal Year Start then select the month

Then to get the calculation started there was no initial members - or 0 the start year 1, also the net numbers added in the first FY was also 0 - I assume that is just a fluke in sample data -

The formulas worked out to be

• CN>>Countd([Joined Date])
• Closed by Year >> Countd([Account Closed Date])
• Net Added >> ([CN]) - ([Closed by year])
• Retention Rate (%) >> (([CE]) -([CN]))/([CS])

Hope this works

Let me know if it helped

Jim

• ###### 2. Re: Member retention calculation - help please

Hi Jim,

Thanks a lot for your assistance with this. I have followed the same process but it looks like I have missed something.

I am getting only a number instead of percentage.

Could you help me with this please?

• ###### 3. Re: Member retention calculation - help please

Hi Sanjay,

Change the default properties for Retention rate to percentage.

Gourav

• ###### 4. Re: Member retention calculation - help please

Thanks Gourav.

• ###### 5. Re: Member retention calculation - help please

Hi Jim,

Thanks for this.

I found this issue with this.

When manually calculated, if we take FY 2014, CE=11, CN=7, CS=9

((CE-CN)/CS)*100 = ((11-7)/9)*100=(4/9)*100 = 44% but it is showing as -200%.  ( total members at 1st July 2013 are 9 and total new accounts created = 7 and total active accounts by 30 june 2014 = 11)

And for FY 2015, CN=4, CE=14,CS=11 so ( (14-4)/11)*100)=(10/11)*100 = 90.9% but it is showing as 100%

FY 2016, CN=6, CE=17, CS=14 so ((17-6)/14)*100 = (11/14)*100 = 78.5% but it is showing as77.78%.

Also if possible could you give me any ideas for implementing on monthly basis as well for FY 2017 which is from June 2016 till July 2017?

Sanjay

• ###### 6. Re: Member retention calculation - help please

Hi Sanjay,

I was thinking of the same yesterday only that the closed accounts should be considered in the finacial year they fall.

Below is the .twbx file where I tried to get details as per expected.

Hope this helps.

Thanks,

Gourav

• ###### 7. Re: Member retention calculation - help please

Hi

Sorry I had expected that the FY designation would work in calculations - it did not - I see that Gourav provided an answer - does it meet your needs

Jim

• ###### 8. Re: Member retention calculation - help please

HI

It disturbed me that I could not get this to work out so I spent some time with it this morning -

See the attached T10.1 workbook - The issue I kept running into was the 2 dates Joined date and Closed date attached to s single member ID - every time I tried to relate the two I simply looked at the common records - not all records

So the solution path was

• First I needed to have a filed for the FY Year - in each the Joined Date and The Closed Date
• Those took the form New year>> IF Month([Account Closed Date])<=6 then Year([Account Closed Date])  else year([Account Closed Date]) +1 end
• Next I duplicated the date set and renamed the 2 Join Date and Closed
• The I created a blended relationship between the to sets that linked the New Joined Year to the New Closed Year
• That gave a way to treat the closed member counts separate from the joined counts but still have a relationship on the dates
• From the JOINED data set I set up the following calculations
• CN>>Countd([Joined Date])
• Running CN>>RUNNING_SUM([CN])
• CE>>[Running CN ]-zn([Close dates].[running closed by year])
• CS>>lookup([CE],-1)
• Retention rate >> (([CE]) -([CN]))/([CS])
• From the CLOSED data set came the following formula
• Closed by year >>{ FIXED [New year closed]:(if Countd(zn([Member ID Closed])) =0 then 0 else Countd([Member ID Closed]) End)}
• Running total closed>> RUNNING_SUM(SUM([Closed by year]))
• Those yielded the worksheet below
• I then hid a few fields to result in the following
• and plotted out the graph

Hope it makes sense - this was a much bigger problem than I first envisioned

Jim

• ###### 9. Re: Member retention calculation - help please

Hi Jim,

I am having trouble with calculating CE as close dates data source is not being recognised.

could you please let me know what was the issue or what I am doing wrong with this?

I am including the twbx file.

Regards

Sanjay

• ###### 10. Re: Member retention calculation - help please

Hi

I think you are almost there-

I looked at the data sources and the connection - it looks correct

The calculated fields are correct

Table calculations work by running along positions in the "table" in the view you are creating -

to do that you need to build the view in the a sequence consistent with the overall formula you are trying to model

In this case you need to apply the calculated fields to the table in the order shown in the attached -