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

    Fredrik Hullberg

      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.