AnsweredAssumed Answered

How to calc lapsed member that did not renew?

Question asked by openspace on Mar 9, 2015
Latest reply on Apr 7, 2015 by openspace

I want to calculate the number of "lapsed" members that did not also renew, in any given year.

 

The function below (which may be familiar to some) calculates the total lapsed members in any given year, but it does not disclude those that renewed their membership in the same year. My question is: how can I adapt this calculation to check if a renewal has been made or not?

 

I have fields in "Line Items" that show the creation, renewal, and start dates for each membership renewal. I'm running fm13. Let me know if I need to provide any other information.

 

Let ( [

  memberIDs =

    ExecuteSQL ( "

      SELECT (\"MContactIds\")

      FROM \"Line Items\"

    WHERE MDateExpiry >= StartDate

 

      " ; "" ; "" )

    ] ;

  Case (

    not IsEmpty ( memberIDs ) ;

    ExecuteSQL ( "

      SELECT COUNT (*)

      FROM Contacts

      WHERE \"CONTACT ID MATCH FIELD\" IN (" & Substitute ( memberIDs ; ¶ ; "," ) & ")

      " ; "" ; ""

    ) ;

 

Screen Shot 2015-03-09 at 11.27.19 AM.png

Outcomes