3 Replies Latest reply on Feb 7, 2019 5:08 PM by Dan Cory

    Determining Memberships Start and Renewal by Month

    Josh Quarles

      Good afternoon,

       

      I am working with Stripe reporting data and I can't seem to produce the report I need.  I am attempting to produce something like the attached workbook based on a few fields; Created Date, Current Period End Date, and Interval.  What I am looking for is a simple report that shows for a series of years, months or even days or for a specific year, month, or day, how many new memberships started and how many existing memberships renewed.  Determining the Start date is based on the Created date.  Determining a renewal requires that you use both the created date to determine the start date, then use the current period end to determine the renewal date.  The problem with the data is that you can't see a historical view of new and renewing memberships without first setting out a huge calendar by day or month.

       

      I created the following to establish a month column, but this requires a column for every month and that I create a parameter for every day?  I have been looking at this too long and can't seem to find a simpler solution.  I hope someone can help.

       

      IF (

              [Interval]='month'

              AND [Created]> [2017_12]

              AND [Created]<= [Parameters].[2018_01])

          THEN 'N'

       

       

      ELSEIF(

              [Interval]='month'

              AND [Created]< [Parameters].[2018_01]

              AND [Current Period End]> [2017_12])

          THEN 'R'

       

       

      ELSEIF(

              [Interval]='year'

              AND [Created]> [2017_12]

              AND [Created]<= [Parameters].[2018_01])

          THEN 'N'

       

       

      ELSEIF(

              [Interval]='year'

              AND MONTH([Created])= 1

              AND [Created] <= [Parameters].[2018_01]

              AND [Current Period End]> [2017_12])

          THEN "R"

      ELSE ""

      END