2 Replies Latest reply on Sep 6, 2018 9:22 AM by Deepak Rai

# Find purchase orders for material that has been ordered by the same user several times within a short time period

I'm trying to build a list that will return all the Purchase Orders that meet the following criteria:

- The same material has been ordered by the same user less than 5 days ago.

So I have the three columns:

- Purchase Order Item

- Purchase Order Date

- User ID

I've been trying to think about different ways to use LoD calculations and substracting one date value from the previous one. This is -ish how I've been thinking:

IF {FIXED [User ID], [Purchase Order Item] : MAX([Purchase Order Date])} -

{FIXED [User ID], [Purchase Order Item] : MIN([Purchase Order Date])} <=5

THEN [PO Number]

ELSE "NULL"

END

Problem is that I don't want to use Max or Min, I want to find each individual purchase order for a certain material, by a certain user and compare the date with the last time this user ordered that material.

Does anyone have any ideas on how to solve this? I've tried to research the Lookup-formula, but I'm not sure that is what I need either.

• ###### 1. Re: Find purchase orders for material that has been ordered by the same user several times within a short time period

Could you attach sample data (twbx is the best) which makes us to assist you quicker.

Thanks,

Shin

• ###### 2. Re: Find purchase orders for material that has been ordered by the same user several times within a short time period

IF DATEDIFF('day',{FIXED User ID, Purchase Item:MAX(Purchase Order Date)},Purchase Order Date)<5 Then "TRUE" Else "FALSE" END