0

Filter a List of SKU's by First Sale Date

Jose Cardenas

August 14, 2019 13:55

None

Follow

I'm trying to filter a list of Stockkeeping Units based on the the first Sale Date (Posting Date) of the item in the Item Ledger Entries. I essentially want a list of only the SKU's that have a first sale date in a user defined date range and omit the rest of the items from the list.

Does anyone know the best way to do this?

Thanks,

Jose

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Bryan Robinson September 06, 2019 12:56

  If there isn't a field in your system that shows first sale date, you will probably need to use NL(Filter) and array calculations: https://support.jetglobal.com/hc/en-us/articles/219403037-Array-Calculations

  1. Create an NL Filter of items sold in the target period
     =NL("filter","item ledger entry","Item No.","Posting Date","01/01/19..01/31/19","Entry Type","Sale") 
     
     
  2. Create an NL Filter of all items sold prior to the target period 
     =NL("filter","item ledger entry","Item No.","Posting Date","..12/31/18","Entry Type","Sale")
     
     
  3. Use NP Difference to remove items sold prior to the target period from the items sold in the target period
     =NP("difference",<<reference to NL filter 1>>,<<reference to NL filter 2>>)
     
     
  4. Create a replicator to list out the item numbers
     =NL("Rows","item","No.","no.",<<reference to NP Difference>>)

  Essentially, find all the items sold in the target period and remove any items that were sold prior to that.  What remains are items sold only in the target period.

  Edited by Bryan Robinson September 06, 2019 12:56