Member Renewal Dates

Allison Moskus 2

Hi all-


I'm trying to query/export (whichever will get me results) on members who renewed during our last fiscal. This is easy enough by just pulling a query for Membership > History > All Rejoined On Dates and All Renewed On Dates for the dates of our FY17. However, the one thing I can't get to pull is the expiration date they had when they made this renewal. Pulling Membership > History > Expires On in the Output just gives me the expiration date they had after said renewal. The reason I need the expiration date from before the renewal is because we are trying to analyze when people are renewing (i.e. the month of their expiration, one month before, etc) and the only way I can think to do this is to get the renewal date next to the expiration date they had when they were going to renew, all in the same report. Does anyone know if this is possible?

Alan French

Can't think of a way to do this directly in RE, but if you export all membership transactions to Excel then you could write an If formula that pulls down the expiry date from the row above if it relates to the same constituent (would require the data to be sorted by constituent ID and expiry date).

Allison Moskus 2

Hi Alan-


Thank you! Do you happen to know where I can find out more info about that specific IF formula? There appears to be many kinds and I can't quite narrow down which one will actually pull the date down from the row above. 

Alan French

Certainly, I can give an example: imagining that I have a list of all membership transactions, with constituent IDs in column A and expiry dates in column B, I could enter the below formula in C2, which compares the constituent ID in cell A2 to the constituent ID in the row above (A1) and returns the previous expiry date (B1) if they are the same or "N/A" if they are not. When you drag this formula down for the entire column, you should find that all your N/As are where it is the first membership transaction for that person. You can change the N/A value in the formula to say anything you like, or even blank.

=IF(A2=A1,B1,"N/A")

Allison Moskus 2

This is great, Alan! Thank you very very much. You're a lifesaver!



Alan French:

Certainly, I can give an example: imagining that I have a list of all membership transactions, with constituent IDs in column A and expiry dates in column B, I could enter the below formula in C2, which compares the constituent ID in cell A2 to the constituent ID in the row above (A1) and returns the previous expiry date (B1) if they are the same or "N/A" if they are not. When you drag this formula down for the entire column, you should find that all your N/As are where it is the first membership transaction for that person. You can change the N/A value in the formula to say anything you like, or even blank.

=IF(A2=A1,B1,"N/A")