Reverse the amount by comparing 5 rows above and below

Hello Experts,

Not sure how to achieve this, I have a scenario in which I have to compare the rows income value for which Code is "ABC-" and Comment is "Int" and then these rows have to compared with above and below 5 rows to check these rows summation whether 1,2,3,4,5 any no. of rows comes out to be equal = 0. In the excel attached , take eg of 200 Income whose summation comes for 1+2+4 row as -200 so it will reverse this amount and this row will not be considered.

Thanks in advance.

SampleforReverse.xlsx

Preparation

Accepted answers

All comments

ferha_jafri

Is it possible in Alteryx... anyone have any idea about this.

ferha_jafri

Did any one face the same scenario or similar scenario. Or in case if it is not possible can experts mention that it is not possible.

ferha_jafri

Till now I am not able to find a solution to this problem it seems alteryx is not capable of such calculation. I am new to alteryx thought all is possible through alteryx but I think the answer is No.

TheOC

Hi @ferha_jafri

This is definitely possible, but it is a tough ask for a computers idea of logic.
This is something I imagine you would have to investigate an R library (and the R tool) for.

Here is a couple of forum posts investigating a similar idea:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Identifying-combination-numeric-cells-which-contribute-to-a/td-p/36944
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Finding-combinations-of-numbers-adding-to-a-given-sum/td-p/65200

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Replicating-quot-Goal-Seek-quot-functionality-from-Excel/m-p/15472#M588

I did have an idea that would work, however it would be really longwinded, in collating the data 2 above and 2 below each row (to get the range of 5 values):

And then you could do a *really* long switch/if-else statement to check each potential. If you think this solution would work I'm happy to build that for you, but it would be an incredible pain if you needed to change it in the future to (for instance) reflect the nearest 10 values, etc

Cheers!
TheOC

ferha_jafri

Thanks atleast you reply not sure about R library solution, I got what you are saying and I am also trying to make combinations of rows it would be great help from you if you can build it in alteryx. This is my first project in alteryx so you can understand the pressure I have and I am not able to find any solution.

Thanks in advance

ferha_jafri

If it is not possible through pure alteryx tool , then is it achievable through R, Python or macros. I saw many posts in which the sum of all rows equivalent to a single value, but no response till now on the solution for this. Is is not feasible in alteryx?

Experts can anyone check this and in case if it is not possible to find a solution can anyone confirm so that I can inform the client that on community nobody is able to provide a solution for this.

Kurohits

HI @ferha_jafri,

I guess this could be possible in Alteryx. If I have understood your question correctly, then you are looking for rows offsetting each other or balancing to 5 above or below rows & these rows should be drop off. Rows which are not balancing or offsetting, are to be considered. Correct me if I am wrong.

If you are looking for something like this, then I have few ideas which you can try:

1) Identify the unique rows (There should be unique key which identifies each row)

2) Create a another new column using formula tool and apply a logic to sum 5 rows up and down.

3) Make another column "Variance" and subtract this column from the column created in previous step. You will get the variance as "ZERO" (0) for rows offsetting each other.

I am not 100% sure that if this solution is feasible for your problem statement. I will have to dig deep in case this won't works.

Thanks

ferha_jafri

Hello , I am new to alteryx so not able to understand as how a new column when added to previous column result to 0, as in this case the combination of rows can be 1+2 or 1+3.... and so on , it is not like summation of 5 rows only needs to be consider

I have a scenario in which I have to take those rows who has Code as "ABC- " comment as "Int" in the final output, then compare these rows income value for which Code is "ABC-" and Comment is "Int" with the above and below 5 rows i.e. total 10 rows, to check these rows summation for same ID come to 0, rows can be 1,2,3,4,5 any no. of rows . In the excel attached , take eg of 200 Income whose summation comes for 1+2+4 row as -200 so it will reverse this amount and this row will not be considered in the final output. Attaching the sample again.

SampleforReverse.xlsx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703