Get the 7 day trailing avg. given a specific date

[breanna_hunt]

Hi Alteryx Community,

I am just getting started and am stumped on how to calculate the trailing 7-day average given a specific start date. I have a file with account ids and a sign-up date and a file with the daily historical VIX(a measure of the volatility of the S&P 500 index).

I want to create a calculated metric for every account id with the average volatility for the 7 days after the sign-up date. Any ideas regarding how I could accomplish this would be greatly appreciated.

Thanks,
Breanna

[BenMoss]

Hi Breanna, I could give this a go but i'm not sure I fully understand what your after. Would it be possible to insert a snapshot on your data and also an example of your desired output?

[breanna_hunt]

Hi Ben -

Thanks for your willingness to help. I've attached the sample data and the output. I also realized I should have said that I am looking for the 1.) Trailing Average to include the signup date and 2.) The signup date can be on a day when the there is not a record for  the VIX(weekend/market holiday) in which case I would want the calculated field to look for the next date in the table.

Thanks again!!

[breanna_hunt]

I didn't see a way to attach multiple files so here is the VIX data.

[jdunkerley79 · Accepted solution]

Hi @breanna_hunt,

I would make a expanded timeseries where weekend and holidays have null entries. You can then easily compute a running count of non-nulls and running total of the average close.

You then need only join back to the record 6 days earlier (as we include the final point) and compute change in count and change in total. The 7 day moving average can then easily be computed.

This moving average can then be joined to the required set of dates to produce the values you need.

Have attached a sample workbook doing the moving average calculation.

James

[breanna_hunt]

Thank you SO much for your help. This is exactly what I needed!