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

# Creating a Date Value List Function

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.

Thank you,

David

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

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

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

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

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

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

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

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

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

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

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

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

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

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 …