# Alteryx Designer Discussions

###### ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

## Dynamic Field Comparison

Highlighted
6 - Meteoroid

Hi,

I'm still new to the software.  I've got the best kind of challenge.  I know exactly how i'd address this with hard codes in the formula tool.  And that's actually fine, but I'm curious if there's a better way to approach it.

My data looks like this.  I've got a grouping on the left, I've got Quarters on top, I've got values in the middle that will all be 1-4.

My objective is to kind of compare when those values change.  IE:

4_2018 = 4

1_2019 = 3

On the most basic level, I'd have formula:  IF [4_2018] != [1_2019] THEN 1 ELSE 0 ENDIF and just sum it up.  I might also do some things with the actual values like IF [4_2018] != [1_2019] THEN [4_2018] - [1_2019] ELSE 0 ENDIF.  I'd repeat so on, so forth [1_2019] vs [2_2019] etc etc.

Here's the catch:

-I'm not married to those dates.  I might add data on either side of it.

-I may also do a couple of more exotic things with those changes

Therefore, if I just attack this with the formula tool I'm going to end up with a ton of hard codes that might be maintained, copy/pasted, extended or whatever.  But I wonder if there's not a better way.

I guess I could trick it into a single batch macro and just plop that on the canvas differently for every series I want to compare.  But I wonder if there's not a way to use the multi-field formula tool which seems to have the right plumbing for cycling through fields dynamically.  Or maybe something else?

Highlighted
Alteryx Certified Partner

Hi @danloz

Great question, and great thinking to keep things dynamic and avoid re-working workflows. I'm sure there are many ways to get this job done, but we're definitely thinking the same re:the Multi-Row Formula as that's the tool I would be using in this kind of scenario.

By first transposing the data, dynamically creating the sort order for your columns, and then using a Multi-Row formula, it is possible to graft the compared data back to the original and account for column formatting with a dynamic rename. This will make the workflow resilient to additional date values, and if you were to change the format of your date columns then you would be looking to recalculate the logic of the column sort order rather than the comparisons - which seems a lot more manageable.

Hope this helps!

Highlighted
6 - Meteoroid

Hey thanks for the tip.  After I downloaded your file I saw how you did the  multi row formula and was able to use the technique you implemented to do a whole bunch of fun stuff.

That dynamic renaming tool is really cool as well.

Labels