Reverse the amount by comparing 5 rows above and below

[ferha_jafri]

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. 

[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-...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Finding-combinations-of-numbers-adding... 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Replicating-quot-Goal-Seek-quot-functi...

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 

[TheOC]

hi @ferha_jafri 

No problem, does my logic fit your problem in the previous solution?

[ferha_jafri]

I will check but in case if you could build the logic and could share the workflow that would be very helpful. 

[TheOC]

hey @ferha_jafri 
The workflow will take a while to create, there are a lot of if-elses to write. Please make sure this fits the solution prior to me creating it.

Thanks,
TheOC

[ferha_jafri]

Honestly I am not sure about the approach as I am very new to alteryx, what my scenario is that I have consider those income for which the code is ABC-  and the comment is INT and then for those rows as outcome I have to check for each row summation with that row and in case if it is 0 , I have to discard that row from the final output. So I would leave to you about the approach as I am not sure about it but this the output looking from me. Hope to get a great solution which is very difficult to achieve from you.

Thanks in advance.