10 Replies Latest reply on Dec 18, 2012 11:23 AM by ddreese

    Custom function help - Days active during date range

    ddreese

      I looked on briandunning.com to see if there was anything that fit my needs, but I have yet to find anything. I'm hoping there is something already done that someone may have used previously, it's a very helpful tool.

       

      What I'm looking for is a custom function that would return the number of days that a record has been "active" over a larger date range. I figure there would be four date inputs:

       

      periodStartDate

      periodEndDate

      openDate

      closedDate

       

      Open and closed dates are the dates associated with that particular record. Closed date can also be empty, which obviously implies that it's still open. Also it appears that the closed date should not be included in the count of active days (a current issue I'm dealing with).

       

      This is an extremely helpful calculation when it works correctly, I can easily be able to say "what records were open on 1/1/2012" and feed that date both into the periodStart and periodEnd and get a list of results right away (records would return 1 day active during that period). Super super useful.

       

      I'm doing something like this currently in a calculation field but I'm planning on adding this to many more tables and if I had a consistent, reliable custom function it would be much easier to manage that way. My calculation is a complete mess and "hacked" over time to try and fix any issues that crop up. I was hoping there was someone here much smarter than me that is good at date math stuff. I apoligize if this is inappropriate, asking for this kind of help here.

       

      Here's my mess of calcuation, I have fields like this all over the database and when I make a change in one I don't remember to change it elsewhere, so it's highly unreliable (and probably mostly incorrect).

       

      Case(

      (GetAsDate(placement_start_date) = GetAsDate(g_date_to)); 1;

      placement_start_date <= g_date_from and IsEmpty(placement_end_date); (g_date_to - g_date_from) + 1;

      placement_start_date <= g_date_from and (placement_end_date >= g_date_from and placement_end_date < g_date_to); (placement_end_date - g_date_from);

      placement_start_date <= g_date_from and (placement_end_date > g_date_to); (g_date_to - g_date_from) +1;

      placement_start_date <= g_date_from and (placement_end_date = g_date_to); (g_date_to - g_date_from);

       

      (placement_start_date >= g_date_from and placement_start_date <= g_date_to) and IsEmpty(placement_end_date); (g_date_to - placement_start_date) + 1;

      (placement_start_date >= g_date_from and placement_start_date <= g_date_to) and placement_end_date >= g_date_from and placement_end_date <= g_date_to; (placement_end_date - placement_start_date);

      (placement_start_date >= g_date_from and placement_start_date <= g_date_to) and placement_end_date >= g_date_to; (g_date_to - placement_start_date) +1;

      "")

       

      Any help would be extremely appreciated! Surely I can't be the first person to find something like this useful!

        • 1. Re: Custom function help - Days active during date range
          BTimm

          The last calculation examples throws me off, as I'm not sure how it relates to the original question. So let me ignore that for now.

           

          If we assume each record has one unique and permanent open date and one unique and permanent close date, then it seems you could do this more simply by building one or more relationships where for example Open date is > or = to PeriodStart and < or = to PeriodEnd. And then just use portals to build reports of records that show up via that relationship.

           

          (The "permanent" factor is key. If open and close dates are changeable and a record can be repeatedly opened and closed, then you would probably need to create a log table to record open and close events and then build the relationships around that. )

           

           

          On the calculation example, I don't really know how to parse that off-hand. :-)  However, I can make a recommendation that you check out the Let function for writing out calculations. It changed my Filemaker life and I'm sure it will for you.

          • 3. Re: Custom function help - Days active during date range
            comment

            ddreese wrote:

             

            What I'm looking for is a custom function that would return the number of days that a record has been "active" over a larger date range.

             

            I don't see why you would need a custom function for this. Try =

             

            Max ( Min ( CloseDate ; gPeriodEnd ) - Max ( OpenDate ; gPeriodStart ) + 1 ; 0 )

             

             

            I am not sure what do you want to happen when one of the fields is empty.

            • 4. Re: Custom function help - Days active during date range
              ddreese

              OK, well I basically shamed myself into writing my first custom function using Let ( ) as BTimm recommended.  I've done Let ()s before, but not often.  To answer your question, BTimm, the start/end dates are permanent, they won't change.  The record itself is basically the log file.

               

              Anyway, here is my function

              http://i.imgur.com/M9Mav.png

               

              The challenge was, if you read the comments, if the end date happens after the date range, I want it to count the last date.  If it happens to be the same day, I do not want to count the last day. 

               

              The application of this right now is tied to money for our fiscal department.  The end date is a day that they do not get paid for, so that's why it becomes an issue.

              • 5. Re: Custom function help - Days active during date range
                ddreese

                Thanks Michael, to be perfectly honest I have no idea how that works - it's like reading another language to me.  It seems to work pretty well with a few exceptions.  I'm trying to not count the last date (close date) if it falls within the gPeriod fields.  To answer your question, blank closed date is fine, it should basically use the gPeriodEnd date + 1 (to count the last day).

                 

                Here are some examples of exceptions where it isn't working at the moment:

                 

                gPeriod 11/1/2012...11/30/2012

                OpenDate:  10/20/2012     ClosedDate:  11/10/2012     Result:  10     Desired:  9     Issue:  Should not count last day if ClosedDate is within the range.

                 

                gPeriod 11/8/2012...11/8/2012

                OpenDate:  11/4/2012     ClosedDate:  11/8/2012     Result:  1     Desired:  0     Issue:  Similar to above, should not count last day if ClosedDate is within the range.

                 

                That said, your solution is extremely elegant, I just wish I could wrap my head around why it works

                • 6. Re: Custom function help - Days active during date range
                  BTimm

                  Yes, once I started using Let function, I've been making commitment to use always and convert as much as I can of old calcs. :-)

                   

                   

                  Another trick I use with Let is always end with

                   

                  ;

                   

                  result

                   

                  )

                   

                  and then just make result=[whatever final calculation]

                   

                  This way, when I'm troubleshooting it's very easy to just swap out variables and test without having to comment out or change the final calc.

                  • 7. Re: Custom function help - Days active during date range
                    comment

                    ddreese wrote:


                    Should not count last day if ClosedDate is within the range.

                     

                    I am not sure I fully understand this condition. Perhaps you just need to start by subtracting 1 from CloseDate?

                     

                     

                    As for how this works, you could break it down to:

                     

                    Let ( [

                    overlapStart = Max ( OpenDate ; gPeriodStart ) ;

                    overlapEnd = Min ( CloseDate ; gPeriodEnd ) ;

                    overlap = overlapEnd - overlapStart + 1

                    ] ;

                    Max ( 0 ; overlap )

                    )

                     

                    where the last part returns 0 instead of any negative result.

                    • 8. Re: Custom function help - Days active during date range
                      ddreese

                      Thank you for the information, I'm going to have to read up on Min and Max to understand how it works.

                       

                      I'll try and explain it better.  If the ClosedDate is within the range of gPeriod dates, it needs to be ClosedDate -1 (we do not count the last day).  If the ClosedDate is after the gPeriodEnd date, it can be the usual calculation because the last day of the gPeriodEnd is paid because it's not closed on that date.

                       

                      Example from above: 

                      gPeriod 11/1/2012...11/30/2012

                      OpenDate:  10/20/2012     ClosedDate:  11/10/2012     Result:  10     Desired:  9     Issue:  Should not count last day if ClosedDate is within the range.

                       

                      gPeriod 11/8/2012...11/8/2012

                      OpenDate:  11/4/2012     ClosedDate:  11/8/2012     Result:  1     Desired:  0     Issue:  Similar to above, should not count last day if ClosedDate is within the range.

                       


                      Is there an easy function that I'm not thinking about to tell if a date is within the range of two other dates?  Thanks for your prompt replies.

                      • 9. Re: Custom function help - Days active during date range
                        comment

                        Please check my logic here:

                         

                        If CloseDate is within the range, subtracting 1 from CloseDate will correct the result.

                        If CloseDate is not within the range, subtracting 1 from CloseDate will not affect the already correct result.

                        • 10. Re: Custom function help - Days active during date range
                          ddreese

                          The second condition doesn't work properly, when CloseDate is empty it returns a zero amount.

                           

                          The function seems to work as desired with the simple change:

                           

                          http://i.imgur.com/9B8nC.png

                           

                          Thank you so much Michael!  This will make my life so much easier!