How to do multi-row calculations across larger numbers of rows?

[zebing]

I am trying to find a way to evaluate trends using the multi-row formula tool. Here's an example of what I want to do: For each row in a column (let's think of one of them for now and call it the active row), I would like to evaluate whether the max of the previous 10 rows is 20% greater than the active row. If it is, I'd like to return the value in the row that is 20 rows after the active row. If it is not, return zero. Is there a way to do such a thing?

[MarqueeCrew]

Here's how I would approach the problem:

You can TRANSPOSE the data into KEY, field name, value sets. Using a FIELD INFO tool from the same input, you can assign column numbers to the field names.

I like to assign a record id and use that as a key. Now when you join on field names you know the record id, field name and number, and the value.

If you are looking for patterns by record id across columns of data, this should be readily accomplished by multi-row formulas or use of running totals.

Cheers,
Mark

[RodL · Accepted solution]

A little different than what @MarqueeCrew suggested (but there are always different ways of approaching a problem in Alteryx), is to Transpose and then use the Group By functionality in the Multi-Row formula.

I've attached a simple workflow where it is comparing to the Max of last 3 rows and then use the value of next row if condition is not met. I haven't fully tested the exact expression, but I think it gives you a basis for building yours.

The key is to make sure you use the Group By in the Multi-Row formula.

[zebing]

Thanks, Rod! You're a champ! That's a clever way to get to a solution that I almost thought I could only really do in excel - and excel was crashing a lot with the big datasets I had. I think Alteryx should make a tool or some functionality that streamlines this process or makes it a bit more obvious. I'll be able to crunch massive amounts of data with this. Fantastic.

Thanks to MarqueeCrew as well - I'm not good enough at Alteryx yet to really fully understand without seeing a workflow. But much appreciated for the responses from both!

[RodL]

@zebing,

Glad this works for you.

I've had new users with a unique process they want to do, tell me on occasion that "Alteryx should make a tool for that."

One of the beauties of Alteryx is that it provides a platform with a relatively limited set of tools in order to make it easier and more intuitive to use. Going down the road of creating a tool for all of the use cases that we've seen would make the platform an overwhelming (to the new user especially) learning experience.

But with that less complex platform, a user can build out just about any business logic they need. So as you work more with Alteryx, you will discover another part of the platform...the power of macros, in which you can encapsulate complex processes into your own "tools". This way you get to choose what you need and create your own personal "category of tools" that are useful to your business environment.

You might want to check out the training resources that are at http://www.alteryx.com/virtual-training under the "past sessions" for macro-related classes.