This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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.