Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

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.  

clipboard_image_0.png

 

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?

 

 

 

 

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. 

 

Snag_45892c9.png

 

 

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