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?

[apathetichell]

There's a bunch of different ways to approach this... I took this as an opportunity to play around with some of the log functions...

[philaw99]

Hi, 

Thanks for helping with this! A little confused about this logic as I am not familiar with log/generate rows tool. Can you help explain why there is a log function and what the generate rows tool is doing?

Also, I notice that your final output is different than what I am looking for. I've attached an updated copy with a "test" column in the sample data above.

[apathetichell]

Multiplying your returns from 1/1 to 1/5 gave me 

1.094958

In Alteryx I got: 1.094957 ---I don't see your Test column sorry.

Alteryx is a bit different than Excel. First off - to create the range, I use generate rows to create a set of dates for each entry in your date range (ie the first date to the last date). I then re-join this to the original data source to get the daily return for each day in the range. Armed with a match key, a range of dates related to that key and the returns for those days - I then have an issue - I can create some kind of iterative macro to multiple the numbers. I can use a python array thingy. I can use multi-row formula to set a base value and then just multiple or I can do something super cool by transforming the returns into logs and then adding them. This works because - MATH. You can do the same thing in Excel btw by using LOG for you D column and getting 

0.002166

0.040207

-0.00218

-0.00305

0.002252

You can then use =10^(cell with the sum of the logs) to get the value... Does that make sense? 

[philaw99 · Accepted solution]

Hi, 

Think it makes sense. I see how you were able to arrive at the numbers. I'm actually having trouble replicating this for a larger data set because some of the "PeriodAgo" dates are NULL() and default to 9999-12-31, so that when I use the generate rows tool im getting the "DateTimeadd (9999-12-31, 1 days) goes outside the supported range of dates" error.

Any insight you can add to the top? Would you be able to provide a solution for an iterative macro as well if the top doesn't pan out? Thanks for all the help!

[apathetichell]

Sure! But just to be clear - I'm catching the Null() exception - it's the period ago at 9999-12-31 that I don't catch... check out the intilization expression  in the generate rows tool and add an "and [Period Ago Date]!="9999-12-31" - basically this sets that specific date to be equal to the original date - so the generate rows iteration doesn't happen.

If you are still getting an error after this - it's because you need to clean your dates (ie you have a string value in your date field)... That will adjust for null()s and 9999-12-31...I just tested it.

Can you mark the solution as correct -thanks!

[philaw99]

Thank you - I've made the correction you suggested but now im getting an error titled "invalid type as operator..."

I've checked all my fields beiing used in the tool and they are all Date fields. Any suggestions here?

[apathetichell]

Can you add a select tool somewhere early on? I think one of your dates might not be date- it would be solid to convert them to a date before the generate rows tool.

[philaw99]

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

This is my initialization expression that is causing the invalid type in operator. Rolling12MAgo is same thing as PeriodAgoDate

[apathetichell]

also make sure you don't have too many or too few quotes