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

    Member retention calculation - help please

    sanjay dandamudi

      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
          Jim Dehner

          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

          • CE>>RUNNING_SUM([Net added during year])
          • CN>>Countd([Joined Date])
          • CS>>Lookup(RUNNING_SUM([Net added during year]),-1)
          • 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
            sanjay dandamudi

            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?

            Retention rate error.jpg

            • 3. Re: Member retention calculation - help please
              Gourav Sharma

              Hi Sanjay,

               

              Change the default properties for Retention rate to percentage.

               

               

               

              Gourav

              • 5. Re: Member retention calculation - help please
                sanjay dandamudi

                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%.

                 

                Could you please help me with this?

                 

                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
                  Gourav Sharma

                  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
                    Jim Dehner

                    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
                      Jim Dehner

                      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
                        sanjay dandamudi

                        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
                          Jim Dehner

                          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 -