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

# Determining Memberships Start and Renewal by Month

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

• ###### 1. Re: Determining Memberships Start and Renewal by Month

HI, Josh

Can you provide a sample workbook with your raw data?

ZZ

• ###### 2. Re: Determining Memberships Start and Renewal by Month

Zhouyi Zhang,

Thank you for the quick response.  Extract attached.

Josh

• ###### 3. Re: Determining Memberships Start and Renewal by Month

It's a little unclear what you want from your description and your data.

Suppose you had a record with Created = 1/1/2017, Ended At = null, Interval = year

Then usually Current Period Start = 1/1/2019, and Current Period End = 1/1/2020. But it looks like there are sometimes when the current period is shifted a little bit, I'm not sure why.

You want this to count as an N on 1/1/2017, and an R on 1/1/2019. Is that right? Are we supposed to count something on 1/1/2018?

If we're not supposed to count something on 1/1/2018, then you can use a pivot or a union to get two copies of the table, one with Created as the date and the other as Current Period Start as the date. Then just count how many events happened on the date.

Dan