Sales Invoice Lines vs Lines in a sales price worksheet

My contract management team as asked for a report that compares how many items that were invoiced in a given month were items that were on a sales price worksheet.

I started with this NL

NL("Count","Sales Invoice Line",,"Posting Date",G$10,"Type","<>@@","Variant Code","@@","No.",NL("filter","Sales Price","Item No.","Starting Date","''|"&H9,"Ending Date",H8&"|''"))

Counting the # of sales invoice lines during the posting period, that had a line type of NOT BLANK, and no variant code. (All variant code items are on the price worksheet and calculated in a seperate cell) where the "No." field of the sales invoice line matched an item number in the sales price table that was open during the posted period. However, i also need to filter the sales price list by the customer number from the sales invoice line in this calculation.

In a seperate calculation, i need to do the above, but not filter by the customer number, but filtering by the customer type code (global dimension 1) thru the dimension value table to get the "CODE" field to match up to the "Sales COde" field in the sales price table.

Any help would be appreciated.

If anything needs clarification or further description, please let me know.

Thanks in advance for your help.


Please sign in to leave a comment.