I have a set of columns that have numbers as their names, from 0 to n (as the actual amount of columns might vary). They each represent values for a specific period of time (so column 0 is the first period, column 1 is the second, and so on).
What I want to do is to calculate, for each row, the absolute % difference between periods, in other words: |(n/n-1)-1|, ending with a table that will have a total o n-1 columns (ideally with a name like “period n vs period (n-1)” or something similar).
If I had a fixed number of columns, this could be done easily with Math Formula, and if the comparison was always vs a same column, the solution with Loop would also be fairly simple, but as the “base of comparison” is changing, I’m not sure how to tackle this.
Absolutely, here it goes! File has a sheet with the mock up dataset and another sheet with how the output should look like (I’ve kept the formulas for your reference). The column headers of the output are more of a nice-to-have rather than a must, anything to resemble that is already useful.
Thank you both for your inputs! @Andrew_Steel I actually didn’t know about Recursive Loops (so many Loops to know!), it’ll sure help me in other flows. However, when running the flow for my data, I got the following error:
I forgot to mention that I might have cells in my dataset that are 0 or blank, and I think this is impacting the flow. Any thoughts on how to correct this?
@armingrudd your solution works regardless of the missing values, so I’ll be following with that!
WOW!
Yes master!
You are the true genius.
Thanks for this. Learned a lot.
Just to be an arch pupil: The current number of loops misses the last column (12 vs 11 in this case) because the end loop condition is “numberOfColumns() - 1” so it does not process the last column.
Of course this is no big deal. I just wanted to make Ivan’s great idea flawless.
Here is the modified expression which gives the desired output:
arr=arrayCreate(abs(column(1)/column(0)-1))
for (i = 2; i < numberOfColumns(); i++) arr=arrayAdd(arr,abs(column(i)/column(i-1)-1))
PS 2: Guys, on a side note, any recommendations on reads about expressions (such as the one in Column Expressions)? Also, any resources on how to use variables in the “Flow Variables” tab of the nodes, especially for Column Filters? I mean, I do know how to use flow variables, but I’m looking for something like the “Node Description” but for the options within a Node’s “Flow Variables” tab. For instance, I always have trouble to apply a variable to “excluded_names”