data analysis

[ntudev]

I'm trying to basically find a way to add a third column that will show me if checks have same codes and if both lastmonthcheck and check have 80 then if there is another check amounts that have the same code but different check amount it  will get tagged as 1. example below. If the code is the same but one of the rows has both 80 then even if the 2nd row (with the same code) is 57 then it should get 1. I hope this makes sense

Code	Check	LastMonthCheck	Max Check
348739	80	57	1
348739	80	80	1
348740	80	80	0
348741	80	57	0
348742	80	57	1
348742	80	80	1
348743	80	57	0
348743	80	57	0

[geraldo · Accepted solution]

@ntudev 

an workflow example

[ntudev]

@geraldo instead of saying 57 how do i put <70?

[geraldo]

@ntudev 

and in this way even you edit the formulas and you change the "==" for < 70 if you include the 70 you put it like this <= 70

[ntudev]

@geraldo why does it only pick up 2 rows? what if i have 100 rows? that have 80 and 57? do i need copy your formula 100 times?

348742	80	57	1
348742	80	80	1

[geraldo]

@ntudev 

The reason for 2 lines is that you need to have the current and previous line to parse.
the rule is always the current and previous line. the next line is always to check if the code belongs to the same code group.

if you need to do a 3 month analysis to make a decision then it would be 3 lines
in your case the comparison is always the current line and the previous line and the next one can be reduced to a single line in the configuration instead of 2

[ntudev]

i cant figure out how to make the below in bold to also have a max check 1, can you help?

Code	Check	LastMonthCheck	Max Check
348739	80	57	1
348739	80	57
348739	80	57
348739	80	80	1
348740	80	80	0
348741	80	57	0
348742	80	57	1
348742	80	80	1
348743	80	57	0
348743	80	57	0