Hi all,
I'm in the middle of converting Excel files into Alteryx workflows and I've run into a bit of difficulty with an INDEX MATCH MATCH formula. It is returning a value based on a date and an index number. This is fairly simple to replicate. However, the level of difficulty comes in the calculation that comes afterward. Once the value is found, a 3-day average is calculated based on the dates immediately before and after the given date. This is where I'm getting tripped up. Below is a screenshot of what my data looks like:
For example, my data gives me a date of 1/24/2019 and an index of TD1. The value here is 25.27. The calculation of the three day average is the TD1 values on 1/23, 1/24, and 1/25 or 25.22.
I'm not sure how I can get the average done. Any help is greatly appreciated.
Solved! Go to Solution.
If you use CReW macros (found here) there is a moving summarize tool that will allow you to compute the moving average.
Cheers,
Mark
I'm looking for more of a workflow based solution since I have to pass this onto others, but your set of macros looks like something I can use in the future.
@johneodell Attached is the moving average workflow with the macro attached. You can right click on the canvas > insert > macros > insert macro to insert the macro attached. This creates a 3 day moving average!
Thanks, @echuong1
This is what I initially thought of doing but my data file has 20 different indexes in it and I was hoping to find a cleaner solution than 20 multi-row formula tools strung together.
How about using this workflow then....
I think I was over complicating things but I did manage to figure out a solution using ideas taken from both or your proposals, but here is what I did.
In my actual workflow I made allowances for dates that may not appear in the index so that I capture everything. Thanks again for all of your help!