Group by ID and market

[keepcalm]

Hi Alteryx community,

I have this dilemma where I am trying to create a new field where if ID number and market the same,  I need to get an amount that is within ten percent of the previous row and to include the "No" and "Yes" in row 5 and 6 in the output given they belong to the same ID & market and fall within 10 percent range.

Given I have multiple records, taking only these 6 records as example I would need to create a new field to help in my output. 

So the final output is to take amount that is within 10 percent range based on previous row regardless of "Yes" or "No". Hope I'm not complicating things but appreciate the community help. Thank you

ID number	Market	Amount Transaction	New field
555	xyz	100
555	xyz	110	Yes
555	xyz	121	Yes
555	xyz	130	No
555	xyz	900	No
555	xyz	990	Yes

[Sebastiaandb]

Hi @keepcalm 

I'm not 100% sure if this is what you're looking for, but it's my best guess :-).

So the rules are:

- If ID number and Market of row 1 and row -1 are the same, compare;

- When comparing, check if the value of amount transaction on row 1 minus row -1 is equal to 10% of the transaction amount of row -1. 

What wasnt clear to me was if the amount had to be exactly 10%,as in your example, comparing 130 to 900 would result in over 10% but you stated is as a no, that's why i kept it at exactly 10%. Let me know if it helps!

Greetings,

Seb

[keepcalm]

hi @Sebastiaandb thanks for shedding some light.

if [Amount Transaction] -[Row-1:Amount Transaction ]/[Row-1:Amount Transaction ] <=0.01 then "Yes" else "No" endif

I am using this current formula above given there's some slight change to requirements in the percentage

If I were to use above formula where percentage is less than or equal to 1% and apply to below table, this should be reflected as "Yes" ( given the final amt is  0.0006.84) but funny is reflected as "No". Am I missing out something?

Amount Transaction

24,071,669.80

24,088,089.35

[keepcalm]

my bad @Sebastiaandb i missed out the bracket. : )

[Sebastiaandb]

@keepcalm haha no problem 😉

Here you go!

Greetings,

Seb