HI Team…
I have the following NL function which returns the correct records but I am in need of only displaying unique records but with the totals of each of those items.=NL("Rows","Sales Line",,"Sell-to Customer No.","XYZ","Shipment Date",E3) - Note E3 is the date filter.
This returns each of the Sales lines for the Customer XYZ. Which also includes the following columns / data.
Customer, Item, Description, Quantity, Planned Dev Date, Add to PO Dated, Shipment Date
Note that there are several records for the XYZ customer with different Items and Quantities. (Disregard the dates as I use a filter to pull the date/date range I need).
XYZ MBLA Medium Brown Large 50
XYZ JBLA Jumbo Brown Large 50
XYZ MBLA Medium Brown Large 25
XYZ JBLA Jumbo Brown Large 20
XYZ MBLA Medium Brown Large 75
The Result I'm looking for would be
XYZ MBLA Medium Brown Large 150
XYZ JBLA Jumbo Brown Large 70
Thank you in advance.
Date
Votes
1 comment
-
Jet Reports Historic Posts If my caffeine has kicked in correctly - I think you are looking for something like this:
change your ROWS command to:=NL("Rows","Sales Line","ITEM NO.","Sell-to Customer No.","XYZ","Shipment Date",E3)This would return a line for each ITEM that was purchased, not each sales line.
The downside to this is that you'll have to change each of your other formulas, the NF key formula type won't work anymore.
But, you can sum up the total quantity easily once you've made this change:=NL("Sum","Sales Line","Quantity","Sell-to Customer No.","XYZ","Shipment Date",E3","ITEM NO.",F3)This assumes that F3 is where your ROWS command is, and that E3 doesn't need to be locked to prevent the number from changing.
Please sign in to leave a comment.