Challenge #17: Month-over-Month Retention Rate

[summit_view]

Got a bit caught up by including closed accounts in the count for open accounts in the later months.  Took some digging around to figure out my mistake.

Spoiler

[FrederikE]

Spoiler

[BenoitC]

Needed to check the solution to see what I was doing wrong!

Mostly used action tools + control parameters

Spoiler

[usmanghayoor]

Spoiler

[Shutaro_Yamauchi]

Tricky definition of retention rate.

We have to consider net increase of customers.

- Denominator: net increase of customers during the period between each month and 24 months before that (half inclusive)

- Numerator: close count of each month

[fluteman]

I cheated a little.

I created a reference file to have the last day of each month and just referred to it over and over again since it was super small; rather than use a formula.

Then

Step 1 : Get things in date format.

Step 2 : Get the floor and ceiling of the time periods Year less 2 years and last day of the month.

Step 3 : The closed Display date 5/1/2013 was easier than I at first thought, just take the month and year and then append 01 to the date to get the display name... example todate("YYYY-MM-01")

Step 4 : create two tables - opened and then closed grouping by open/close type use UNION on these two tables

Step 5 : Crosstab with a formula to get your retention rate.

Cheers

Fluteman

[Jon-Knepper]

Spoiler

 Here is my solution!

[alexm06]

My solution attached

[scoles0617]

My solution:

[TonyAndriani]

Reposting to my new user ID.