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
Solved! Go to Solution.
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?
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!!
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
Thank you SO much for your help. This is exactly what I needed!