0

Filter trouble - first shipment date & new items

I'm trying to make a report that shows revenue from all new items over the prior 12 months, and compare that to prior values by month. I'm defining "New" in this case as first sale occurring within the prior 12 months of the target date.

I'm struggling to create a concise filter that can determine when an item's first shipment occurred (New or Old item), and make a list of those item numbers. I have a solution that works for a single period, but doesn't scale well for multiple time periods. Here I'm just hiding the rows where the first posting date is more than 365 days past. I don't want to generate the full item list for multiple date periods.

That solution:
=nl("Rows","item","no.","no.",$D$5,"-Sales ($)",">0")
=nl("first","item ledger entry","posting date","Item No.",$E13,"Entry Type","sale")
=nl("Sum","Sales Invoice Line","Amount","No.",$E13,"Shipment Date",$D$7)

Ideally, I could run that 3rd NL function off of a filter that gives all the applicable item numbers.

Thanks! :)

0 comments

Please sign in to leave a comment.