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â
