Trying to do the EXCEL Product Function Based on a Date Range

[philaw99]

Hi all,

I'm trying to calculate a cumulative return using a function like EXCEL PRODUCT only if the return falls on a date range. 

For example, if the "date" is 1/5/2022 and the "period ago date" is 1/1/2022, I'd like to do the EXCEL product function for all the returns from 1/1/2022 to 1/5/2022. I AM NOT looking for a rolling period calculation, but rather a calc based on the date range.

The same will apply for the next row, but just a shift in date rage (from 1/2 to 1/6), and so on. Ideally, this would be output into a new column titled "Period Ago Cumulative Return." Will be doing this on a larger scale of data. How can I best accomplish this?

[philaw99]

Is there anything off you see with the above expression?

[apathetichell]

if [Rolling12MAgo] != null() then [Rolling12MAgo] else [ActivityDate] endif and [Rolling12MAgo] != '9999-12-31'

should be

if [Rolling12MAgo] != null() and [Rolling12MAgo] != '9999-12-31' then [Rolling12MAgo] else [ActivityDate] endif 

sorry - two and clauses...

[philaw99]

Hi,

Now I'm running into the "DateTimeAdd (9999-12-31,1 days) goes outside the supported range of dates" error after using your new function.

Double checked that all incoming fields for this Generate Rows tool are date fields. Any further advice?