INDEX MATCH MATCH equivalent

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.

Help

Accepted answers

johneodell

@MarqueeCrew @echuong1

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!

Example.yxmd

All comments

MarqueeCrew

@johneodell ,

If you use CReW macros (found here) there is a moving summarize tool that will allow you to compute the moving average.

Cheers,

Mark

johneodell

@MarqueeCrew

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.

DiganP

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

Moving Average.yxmd

MovingCalculation.yxzp

echuong1

I would use a multi-row formula to calculate the average value for each date in your index table and then join your other table using the date field. See attached for an example.

Let me know if that works!

New Workflow1.yxmd

johneodell

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.

MarqueeCrew

@johneodell ,

How about using this workflow then....

Index Match Match Equivalent.yxmd

johneodell

@MarqueeCrew @echuong1

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!

Example.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703