Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

INDEX MATCH MATCH equivalent

johneodell
8 - Asteroid

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:

 

johneodell_0-1576697823113.png

 

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.

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
johneodell
8 - Asteroid

@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
Alteryx Alumni (Retired)

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

 

DiganP_1-1576699685989.png

Digan
Alteryx
echuong1
Alteryx Alumni (Retired)

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!

 

echuong1_0-1576699761228.png

johneodell
8 - Asteroid

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
20 - Arcturus
20 - Arcturus

@johneodell ,

 

How about using this workflow then....

 

capture.png

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
johneodell
8 - Asteroid

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

johneodell_0-1576705602115.png

 

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!

Labels