PO Split Analysis on Rolling 7 Day Period

[clm24]

I'm trying to perform a PO split analysis on a rolling 7-day period by identifying which POs have the same vendor for the same amount. My data looks similar to this: 

PO Number	Vendor Name	Create Date	Amount	Company Code
1	ABC	1/4/24	10	L
2	DEF	2/1/24	27	J
3	ABC	1/6/24	10	L
4	GHI	3/15/24	19	P
5	JKL	7/22/24	15	N
6	ABC	1/1/24	10	L

In this case the threshold for additional approval is 30. The goal is to identify that PO numbers 1, 3, and 6 are all by the same vendor, for the same amount, in a 7-day period where the total equals 30 but the POs were split to bypass additional approval. Thank you!

[usmanbashir]

@clm24 - what is expected if there's another PO for ABC on 1/9/2024?

[clm24]

It is expected that we identify PO 1, 3, and the new PO on 1/9/24 as a group within the 7 day window that have the same vendor and the same amount. Identifying the clusters with the criteria. 

[usmanbashir]

@clm24 - please see attached workflow. Hopefully this is what you were looking for!

[clm24]

By creating a formula to create the week number, this doesn't allow us to perform the analysis on a rolling basis. For example, week 1 is 1/1/24 - 1/7/24 and week 2 is 1/7/24 - 1/14/24. How do we identify if there are POs hitting our criteria from 1/4/24 - 1/11/14 when we've defined weeks so strictly? Perhaps I'm misunderstanding. 

[usmanbashir]

Ahh gotcha. I think an iterative macro will be needed. 

[clm24]

Hm okay. I'm not very familiar with how to write macros, especially in Alteryx. Is this something that you can help with?