9 Replies Latest reply on Nov 18, 2016 3:46 PM by Maximus Decimus Meridius

    Plotting cumulative cancellations by tenure (workbook attached)

    Maximus Decimus Meridius

      I want to see cancellation % by tenure. So for instance, the 10-day cancellation rate would only consider members who have been members for 10 days or more (tenure >= 10), and then give the fraction of members who cancelled in the first 10 days vs the ones who stayed 10 days or more.

       

      Similarly the 30 day cancellation rate looks at members who joined 30 days or more ago, and what fraction of them cancelled in the first 30 days.

       

      This way I should be able to get a graph that shows cumulative percent of members who cancel over time

       

      So it would show the cumulative % of cancelled members by tenure, so we can look and see after 2 weeks x% of our members have cancelled. I have previously been doing this in R to produce graphis like this:

       

      but I want to try to recreate this in tableau (without R code).

       

      Thanks for any help!

        • 1. Re: Plotting cumulative cancellations by tenure (workbook attached)
          Shinichiro Murakami

          At first, pivot data thru edit data source.

           

           

           

          Create Parameters with dates.

           

           

          Create several calculated fields.

           

          [Enroll ID]

          if [Pivot field names]="Enroll Date" then 1 else 0 end

           

          [Canceled ID]

          if [Pivot field names]="Cancel Date" then -1 else 0 end

           

          [Canceled ID negative]

          [Canceled ID]*-1

           

          [Cum ID]

          [Enroll ID]+[Canced ID]

           

          [Tenure at Start]

          {fixed : sum(if [Pivot field values]<[Start Date] then ([Cum ID]) end)}

           

          [Tenure at date]

          attr([Tenue at Start])+running_sum(sum([Cum ID]))

           

          [Date Filter]

          if [Pivot field values]>=[Start Date] and

          [Pivot field values]<=[End Date]

          then "Show" else "Hide" end

           

           

          Put table calculation for [Canceled ID] and [Enrolled ID]

           

           

           

          Thanks,.

          Shin

          • 2. Re: Plotting cumulative cancellations by tenure (workbook attached)
            lei.chen.0

            Hello Maximus,

             

            This is a little bit like cohort analysis.

             

            Besides Shinichiro Murakami 's solution, here comes another approch, LOD+Table Calculation.

             

            I attached a sample workbook and a reference link.

            Top 15 LOD Expressions | Tableau Software

             

             

            Regards

            • 3. Re: Plotting cumulative cancellations by tenure (workbook attached)
              Shinichiro Murakami

              Thank you Lei Chen.

              I mistook the goal.  it should be the days from enrollment, not date..

              My bad.

               

              Regards,

              Shin

              • 4. Re: Plotting cumulative cancellations by tenure (workbook attached)
                Maximus Decimus Meridius

                Thanks, having a look at this first before I start digging through Shinichiro's solution. This looks like it's listing the number of cancels at each tenure (x people cancelled on day y), which I guess can also be achieved by showing the running total of records by tenure (where tenure = cancel date - enroll date)? I do get the same graph as you with this approach

                 

                What I'm trying to show though is the value as % of total of all records in that cohort, i.e. at day 90, 13% of everyone with who joined 90 days ago or more have cancelled etc. I tried just dividing by total(sum([number of records]), but that only summed up cancels (so at day 194 it gives 100%). Do you know how this could be achieved?

                 

                This is what I used in R

                 

                sapply(seq(1:500), function(x) sum(data[data$tenure < x,]$members, na.rm = T)/sum(data[data$life >= x,]$members))

                 

                That is, for each value of x from 1 to 500, I compute the number of people who cancelled within x days, divided by the total number of members who joined at least x days ago (here tenure means days of membership before cancelling, while life means how many days ago they joined). This gives me a vector of percentages that I can then plot to see where in the membership we have spikes in cancellations, which yields the graph mentioned in the first post. Thing is in R there's some work involved breaking this cancellation out by other variables, whereas in tableau, if I could figure out a way to compute this I'd be able to just drag and drop to get the cancellation curves by age, state and all kinds of things very easily.

                 

                Thanks again for the help!

                • 5. Re: Plotting cumulative cancellations by tenure (workbook attached)
                  Chris Chalmers

                  Is this what you're looking for? (bottom graph, workbook attached)

                   

                  Cancellation Rate.PNG

                  • 6. Re: Plotting cumulative cancellations by tenure (workbook attached)
                    Maximus Decimus Meridius

                    Thanks, this is super helpful but man this is turning out to be way harder than I thought...as a sanity check, you can see that 202,743 have a life of 91 days or more, and out of those 27,226 cancelled in the first (had tenure less than or equal to) 90 days. So the 90 day cancellation rate should be 13.43%

                     

                    So in your book 'Running Count of Tenure < x' is counting everyone with tenure less than x, but it should only count those who also have life greater or equal to x. 'Running Count of Life >= x' is showing 296K having life of 198 or greater, but the real number of records with 198 life (the max life) is 1.9K

                     

                    Basically the formula I'm trying to compute is this:

                     

                    (# of records with life >= x AND Tenure < x)/(# of records with life >= x)

                     

                     

                    I managed to get the correct count of life >=x by using

                     

                    total(sum([Number of Records]))-RUNNING_SUM(SUM([Number of Records]))

                     

                    and then plotting against life + 1 (which gives the numerator). But I have no idea how to get the total of people with tenure < x and life >= x for the denominator...

                     

                    Thanks again for the help

                    • 7. Re: Plotting cumulative cancellations by tenure (workbook attached)
                      Chris Chalmers

                      Your visualization is challenging because it requires combining two table calcs operating on differently-ordered data (tenure and life) onto the same axis. LOD and table calcs don't mingle well. I can get your desired numerator and denominator on separate sheets, but I've exhausted my knowledge of the product trying to merge them onto one axis. Hopefully someone knows a trick and can shed some light.

                       

                      Best of luck,

                      -Chris Chalmers

                      • 8. Re: Plotting cumulative cancellations by tenure (workbook attached)
                        Shinichiro Murakami

                        OK, my turn again.

                         

                        Still not sure I understand the request very correctly, but here is some trial.

                         

                         

                        [Days]

                        max(0,[Cancel Date]-[Enroll Date])

                         

                        [Days 2]

                        if isnull([Cancel Date])

                        then today()-[Enroll Date]

                        else [Days] end

                         

                        [Cancelled or Not]

                        if isnull([Cancel Date]) then "Existing" else "Cancelled" end

                         

                        [Percentage]

                        running_sum(count(if [Cancelled or Not]="Cancelled" then [Member ID] end))

                        /

                        (attr({fixed:count([Member ID])})-running_sum(count(if [Cancelled or Not]="Cancelled" then [Member ID] end)))

                         

                         

                        Thanks,

                        Shin

                        • 9. Re: Plotting cumulative cancellations by tenure (workbook attached)
                          Maximus Decimus Meridius

                          Thanks for keeping trying, but after hours trying to make this work I'm just not seeing a way. The problematic part is that I need to count the number of people who have cancelled under x days, but only from the members who have joined at least x days ago. The issue is that at 91 days it says 36.5K cancelled, but these are people who cancelled in the first 91 days out of everyone, so it neglects the cohort approach. The real number I've been trying to get to is 27K.

                           

                          People who have only joined a few days ago will have almost 0% 90 day cancellation rate, even though a good chunk of them will actually end up cancelling in the first 90 days, so it skews the data if you don't filter on minimum life...

                           

                          I really can't think of a way around this