1 2 Previous Next 17 Replies Latest reply on Apr 7, 2015 8:06 AM by openspace

    How to calc lapsed member that did not renew?

    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

        • 1. Re: How to calc lapsed member that did not renew?
          perren

          You could try to change the WHERE clause in the memberIDs variable calculation from:

           

          WHERE MDateExpiry >= StartDate

           

          to:

           

          WHERE MDateExpiry >= StartDate AND YEAR ( RenewalDate ) <> YEAR ( MDateExpiry )

           

          Change "RenewalDate" to whatever your field is named as needed.

           

          Hopefully it's as easy as that, but this is off the top of my head, so I might have syntax errors somewhere...

          • 2. Re: How to calc lapsed member that did not renew?
            openspace

            Thanks Perren. I don't think this would work since the field does not contain the renewal date of the new "renewal record" it would not be able to reach the other field that has the new date. This is confusing to explain, I hope I can illustrate this clearly.

             

            Ex.

             

            1. Jill (MDateRenewal= 2013-02-01 ; MDateExpiry=2014-03-01)

            2. Joe (MDateRenewal= 2013-02-01 ; MDateExpiry=2014-03-01)

            3. Joe (MDateRenewal= 2014-03-01 ; MDateExpiry=2015-03-01)


            Desired result of members lapsed: 1


            From the example above, if my start date is 2014-01-01 and my end date is 2014-12-30 then Jill is the only lapsed member since she did not renew.

             

            Using the calculation above, the SQL selects the fields in the first two lines without knowledge of the third.

            • 3. Re: How to calc lapsed member that did not renew?
              perren

              Looks like I had it backwards...I was comparing renewal date to expire date, not start date.

               

              With the above example data set this should return the opposite:

               

              WHERE MDateExpiry >= StartDate AND YEAR ( RenewalDate ) <> YEAR ( StartDate )

               

              Any better?

              • 4. Re: How to calc lapsed member that did not renew?
                openspace

                hmm.. I adapted my function to calculate all renewals that fall after the start date (that are not new) as well as all the lapsed members that fall after the start date. Then I simply subtract: expired - renewed. The numbers match except for my calculation returns three figures as two (missing a carriage return), now I have to figure out how to turn this back into three numbers, and also clean this up a bit. There has to be a way to clean up this mess

                 

                Let ( [

                 

                 

                membersexpired= 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 ; ¶ ; "," ) & ")

                       " ; "" ; ""

                    ) ;

                    0

                  )

                ) ;

                 

                 

                membershipsdollarsexpired = ExecuteSQL ("

                SELECT COUNT (DISTINCT MContactIds), SUM (MTotal)

                FROM \"Line Items\"

                WHERE MDateExpiry >= StartDate

                " ;¶; ¶) ;

                 

                 

                membersrenewed= Let ( [

                  memberIDs =

                    ExecuteSQL ( "

                      SELECT (\"MContactIds\")

                      FROM \"Line Items\"

                      WHERE MDateRenewal >= StartDate

                      " ; "" ; "" )

                    ] ;

                  Case (

                    not IsEmpty ( memberIDs ) ;

                    ExecuteSQL ( "

                      SELECT COUNT (*)

                      FROM Contacts

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

                       " ; "" ; ""

                    ) ;

                    0

                  )

                ) ;

                 

                 

                membershipsdollarsrenewed = ExecuteSQL ("

                SELECT COUNT (DISTINCT MContactIds), SUM (MTotal)

                FROM \"Line Items\"

                WHERE MDateRenewal >= StartDate

                " ;¶; ¶)

                ] ;

                 

                 

                membersexpired - membersrenewed  & ¶ & membershipsdollarsexpired - membershipsdollarsrenewed)

                 

                Returns:

                33

                331905

                 

                Desired Result:

                33

                33

                1905

                • 5. Re: How to calc lapsed member that did not renew?
                  openspace

                  I changed the last line to following, seems to fix the carriage return. still a lot of code to chew though.

                   

                  membersexpired - membersrenewed  & ¶ & GetValue(membershipsdollarsexpired;1) - GetValue(membershipsdollarsrenewed;1 ) &¶ &  GetValue(membershipsdollarsexpired;2) - GetValue(membershipsdollarsrenewed;2) )

                  • 6. Re: How to calc lapsed member that did not renew?
                    user19752

                    You seems not using EndDate, is there some rules on data like

                    EndDate = StartDate + (about) 1year

                    MDateRenewal should be in another records of MDateExpiry

                    • 7. Re: How to calc lapsed member that did not renew?
                      beverly

                      This post may be related to:

                      SQL: Returning ? for 1/3 stats

                       

                      beverly

                      • 8. Re: How to calc lapsed member that did not renew?
                        openspace

                        No I have an end date field, but I'm not sure how I could use it.

                         

                        My main issue now is that my numbers for lapsed members and memberships add up but the total $$ amount does not.

                        • 9. Re: How to calc lapsed member that did not renew?
                          user19752

                          Get members while startDate and endDate

                              SELECT (\"MContactIds\")

                              FROM \"Line Items\"

                              WHERE MDateExpiry >= StartDate AND MDateRenewal <= EndDate

                           

                          Get members when endDate

                              SELECT (\"MContactIds\")

                              FROM \"Line Items\"

                              WHERE MDateExpiry >= EndDate AND MDateRenewal <= EndDate


                          So difference of the results is "lapsed at endDate"


                          SELECT l1.\"MContactIds\"

                          FROM \"Line Items\" l1 LEFT JOIN \"Line Items\" l2 ON ( l1.\"MContactIds\" = l2.\"MContactIds\" )

                          WHERE

                          l1.MDateExpiry >= l1.StartDate AND l1.MDateRenewal <= l1.EndDate

                          GROUP BY l1.\"MContactIds\"

                          HAVING COUNT(CASE WHEN l2.MDateExpiry >= l2.EndDate AND l2.MDateRenewal <= l2.EndDate THEN 1 END) = 0

                           

                          This SQL may be slow since COUNTing all l2 records, but I can't get other type of queries using sub query run on FQL yet now.

                          • 10. Re: How to calc lapsed member that did not renew?
                            openspace

                            user19752 helped me solve the last piece of this puzzle in this thread: Re: SQL: SELECT SUM of column2 in relation to column1

                             

                            And so I've decided to mark his last post as the correct answer, but please refer to the thread above for the answer in full.

                             

                            Thanks to everyone else that responded, I always learn a ton from all these suggestions!

                            • 11. Re: How to calc lapsed member that did not renew?
                              openspace

                              user19752 helped me solve the last piece of this puzzle in this thread: Re: SQL: SELECT SUM of column2 in relation to column1

                               

                              And so I've decided to mark his last post as the correct answer, but please refer to the thread above for the answer in full.

                               

                              Thanks to everyone else that responded, I always learn a ton from all these suggestions!

                              • 12. Re: How to calc lapsed member that did not renew?

                                The complexity of the replies is quite amusing since such complexity isn't needed, only native FileMaker tools are required.

                                 

                                If you have a hammer, everything looks like a nail. If you use SQL, every problems seems to demand it...

                                 

                                Your design graph has a lot of tables in it but the question only needs two tables for the answer.

                                 

                                First create your customer table and record.

                                Next create a related table for subscriptions.

                                 

                                Each paid subscription will update the customer record. The customer record will have among others the desired fields.

                                 

                                Current Paid Subscription Date:

                                Length of Subscription:

                                Expiration Date: (You do the calcs)

                                Cancelation Date:  (Your script enters the date here after it runs through your customers. You will want to send renewal notices, reminders, etc.)

                                 

                                You need these scripts/layouts:

                                New subscription

                                Renewal

                                Cancellation

                                Notices

                                Daily/weekly review of accounts for notices and cancelations

                                    Send notice

                                    Cancel Account

                                 

                                When a subscription is entered, you update these three fields in your customer account using your script.

                                Your daily renewal script checks the expiration date and does its work sending notices etc. When you determine a cancellation you enter the cancelation date.

                                 

                                Now you have the data in the fields you want for your report.

                                 

                                To make it work you would create a few Year and Month Fields:

                                Year Paid

                                Month Paid

                                Paid Counter = 1

                                Year Expire

                                Month Expire

                                Expired Counter = 1

                                Year Cancel

                                Month Cancel

                                Cancel Counter = 1

                                 

                                These fields are the basis for your reports and you can make your summary counts based on Years and Counters.

                                 

                                Two types of reports, one with body for names and one without for just totals.

                                 

                                With FileMaker 13 you can make one report with many break fields and only show the desired break fields by how you sort the report. In other words, if your report has the year for Paid, Expired and Canced and a summary field on those lines for Counter, you can sort one one or all of the fields and see the appropraite summaries. You could also design the file and report to show a column report by year for the three categories..

                                 

                                You can print the reports to PDF or even export to a FileMaker flat file. You can also grab the summaries and put them into another table for other uses.

                                 

                                If you want a permanent record you can run through you customer table whenever and import those fields I list into another table which will store the data.

                                 

                                So, using only FileMaker tools you can solve your problem. You just have to know how to create the simple, productive design. Make your design a correct design before you begin adding all the bells and whistles or you will get lost playing with them.

                                • 13. Re: How to calc lapsed member that did not renew?
                                  openspace

                                  where have you been the last few weeks!☺

                                   

                                  Is my table structure really that terrible? The diagram above is my entire structure and does not apply only to this problem. I also cannot take credit for its entirety since I adapted the design from a file maker starter solution for ease of development and also learning. I appreciate your comments though some well made points.

                                  • 14. Re: How to calc lapsed member that did not renew?

                                    I assume you mean me?

                                     

                                    OK, your question is not unusual and I find that seldom does it get answered in a fashion I consider correctly, so I went to the drawing board and designed my own version. It uses two tables. I did it using only FileMaker native tools and in less than half an hour.

                                     

                                    It features a customer card that shows the latest new subscription plus later renewals.

                                     

                                    My apologies for not making it 'pretty' but that often leads one off the deep end. When I finish, I will make it pretty.

                                     

                                     

                                    Subscription Customer.png

                                    The subscription portals are just one table. A little trick shows the current subscription trail. Notice how in 2014 the subscription was canceld and then 'New"ed.

                                     

                                    The scubscription table also allows the counting of subscriptions and cancellation by year, year and month, or year plus month. Click on blue button to determine the sort. The sort defines the report. One report layout, three reports.

                                     

                                    Subscription Report.png

                                     

                                    All the work is done with a few calculated fields of the right sort in the right place, one report layout with three sorts. No SQL, no nothing but FileMaker functions.

                                     

                                    The real trick is in knowing how FileMaker works and leveraging that to your needs.

                                     

                                    Here's my graph:

                                     

                                    Subscriptions Diagram.png

                                     

                                    Now that it works I will need:

                                    A script to look for customers to send notices and to cancel the contracts.

                                    A script to enter new payments.

                                    A report script to find the range and sort it.

                                    1 2 Previous Next