Get the 7 day trailing avg. given a specific date

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

Date Time

Preparation

Accepted answers

jdunkerley79

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

Moving Average.yxmd

All comments

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!!

Trailing7DayAvgExampeOutput.xlsx

breanna_hunt

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

historicalDailyVix.csv

jdunkerley79

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

Moving Average.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703