nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Preparation

Expression

Accepted answers

RodL

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.

Testing MultiColumns with MultiRow Formula.yxmd

All comments

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

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

Testing MultiColumns with MultiRow Formula.yxmd

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!

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15911

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703