14 Replies Latest reply on Oct 9, 2014 12:16 PM by rothdavid

    Creating a Date Value List Function

    rothdavid

      I have a project where I need to make a value list of dates based on start date and end date. Once I have a value list I can then select a value position based on other criteria.

       

      A date range on the record may be: (start) 1/1/2014 to 4/15/2014 (end)

       

      The list created and stored in a field or var would always be for the first day of each month for the duration of the date range:

       

      1/1/2014

      2/1/2014

      3/1/2014

      4/1/2014

       

      I'd like this to occur as a calc or CF, I'd rather not script this to create a variable because there may be a few hundred needed to generate.

       

      Any comments or advice, approach is welcome.

       

      Thank you,

       

      David

        • 1. Re: Creating a Date Value List Function
          Mike_Mitchell

          Here's one possibility:

           

          /*

          cfListOfDates ( startDate ; endDate ; result )

           

          startDate = First date of range

          endDate = End date of range

          result = Initially empty when called; used for recursion

          */

           

          Let ([

          firstOfStart = Date ( Month ( startDate ) ; 1 ; Year ( startDate )) ;

          firstOfNext = Date ( Month ( startDate ) + 1 ; 1 ; Year ( startDate ))

          ] ;

           

          Case (

           

          GetAsDate ( endDate ) < firstOfStart ; result ;

           

          cfListOfDates ( firstOfNext ; endDate ; List ( result ; firstOfStart ))

           

          )

          )

           

          Note that you should call it like this:

           

               cfListOfDates ( "1/1/2014" ; "4/15/2014" ; "" )

           

          (enclosing the dates in quotes and sending in a null string for the "result" parameter)

           

          HTH

           

          Mike

          • 2. Re: Creating a Date Value List Function
            erolst

            Without a dummy argument:

             

            ListFirsts ( dateBegin ; dateEnd ) =

             

            Let ( [

              ~s = dateBegin ;

              ~m = Month ( ~s ) ;

              ~y = Year ( ~s ) ;

             

              this = Date ( ~m ; 1 ; ~y ) ;

              next = Date ( ~m + 1 ; 1 ; ~y )

              ] ;

              List ( this ;

                Case ( next <= dateEnd ;

                ListFirsts ( next ; dateEnd )

                )

              )

            )

            1 of 1 people found this helpful
            • 3. Re: Creating a Date Value List Function
              rothdavid

              Mike,

               

              Thank you very much.  I just used this function and it works perfectly !   Reading this, I think I finally grasp the concept of using an the empty var for recursion.

               

              Thank you for your assistance !

               

              David

              • 4. Re: Creating a Date Value List Function
                Mike_Mitchell

                The reason for the dummy argument is to enable tail recursion. Without it, you're limited to 10,000 iterations. Might not matter in this case, but I make it routine to utilize tail recursion just in case.

                • 5. Re: Creating a Date Value List Function
                  erolst

                  rothdavid wrote:

                  Reading this, I think I finally grasp the concept of using an the empty var for recursion.

                   

                  Try to grasp also that it isn't necessarily required, if you have other rmeans to check the exit condition.

                  • 6. Re: Creating a Date Value List Function
                    rothdavid

                    I see that in your cf, placing the case statement inside the LIST function generates this list too.  Thank you erolst too for your approach. Both have helped me undertand how to appoach items like this.

                     

                    Thank you,

                    David

                    • 7. Re: Creating a Date Value List Function
                      Mike_Mitchell

                      It's worth noting that this will still work fine as a script; just pass in the dates as parameters and use Exit Script to return the list as a result. There probably won't even be much of a performance difference, and you won't have the recursion limit to deal with.

                       

                      The only real advantage a custom function has over a script is the fact that you can use it inside a calculation. So unless you need that, don't be afraid to use a script for this sort of thing.

                      • 8. Re: Creating a Date Value List Function
                        erolst

                        Mike_Mitchell wrote:

                        don't be afraid to use a script for this sort of thing.

                         

                        A looping script also has the advantage that it's (usually) easier to understand (and test) than a recursive function.

                        • 9. Re: Creating a Date Value List Function
                          erolst

                          Mike_Mitchell wrote:

                          I make it routine to utilize tail recursion just in case.

                           

                          Right, but I hate dummy arguments

                          • 10. Re: Creating a Date Value List Function
                            rothdavid

                            I'm good with scripts and could have written this as a script easily enough.  This time it made sense to have this done in a calc however. I may be processing a few hundred records with date ranges spanning a year or two.  This calc removes all those repetitive scripting steps.

                             

                            Thanks guys, I appreciate your assistance with this.

                             

                            David

                            • 11. Re: Creating a Date Value List Function
                              Mike_Mitchell

                              Uh ... what repetitive script steps are you referring to? Let's assume (just to avoid starting the Great Multiple Script Parameter Debate) that you store your start and end dates in a pair of global fields. Your script might look something like this:

                               

                              Set Variable [ $currentStart ; table::startDate ]

                              Loop

                                   Set Variable [ $firstOfStart = Date ( Month ( $currentStart ) ; 1 ; Year ( $currentStart )) ]

                                   Exit Loop If [ $currentStart > table::endDate ]

                                   Set Variable [ $result ; List ( $result ; $firstOfStart ]

                                   Set Variable [ $currentStart ;  Date ( Month ( $currentStart ) + 1 ; 1 ; Year ( $currentStart )) ]

                              End Loop

                              Exit Script [ Result: $result ]

                               

                              That's it. It's remarkably similar to the CF, but, as erolst pointed out, easier to troubleshoot and doesn't run into the recursion limit.

                               

                              What am I missing?

                               

                              Edited for a variable error.

                              • 12. Re: Creating a Date Value List Function
                                rothdavid

                                Mike, Its just a comment in general, using a script to loop through sequences must have more system/memory overhead than using a CF to achieve the same result.

                                • 13. Re: Creating a Date Value List Function
                                  erolst

                                  Mike_Mitchell wrote:

                                  It's remarkably similar to the CF

                                   

                                  It's the same algorithm, just in “a different language” (yeah, well …); actually, you could write the entire script as:

                                   

                                  Loop

                                       Exit Loop If [ use logic from CF calculation, adapt from Let() cars to $vars as necessary, accumulate results into $result, and return Boolean as immediate result for exit condition check ]

                                  End Loop

                                  Exit Script [ Result: $result ] # Set Field [ … ; $result ? ]

                                   

                                  and the only differences are that we need the Loop statement to drive the recursion – plus a mechanism to store or pass the resulting list. (Never mind that this is sub-optimal due to either performing the assignment of intermediate variables within the loop, or the access to stored fields).

                                  erolst wrote:

                                  A looping script also has the advantage that it's (usually) easier to understand (and test) than a recursive function.

                                  With the construct outlined above, this would then be a thing of the past …

                                  • 14. Re: Creating a Date Value List Function
                                    rothdavid

                                    Great thread we've created today, thank you both for your commentary.