Why does the formula below have an apostrophe at the beginning when used with NL Table? Even if I put the "=" in from of the excel formula it still puts the apostrophe at the front.
=NP("Formula","MAXIFS(SalesShipmentLine[Posting Date],SalesShipmentLine[Location Code],[Location Code],SalesShipmentLine[Sales Order],[Sales Order],SalesShipmentLine[Item],[Item],SalesShipmentLine[Shipment Date],[Shipment Date])")
Hello Todd -
What type of data source are you using?
Database
Hi Todd -
The NP(Formula) function would need to reference fields being returned by your NL(Table) function.
Here is a simple example showing the syntax:
I did make that change. It still does the same thing. Could it be related to the fact that the result of the MAXIFS comes from another table created at run time? Thanks
It's possible.
I'm not sure what you are trying to do. I think your best bet is to run this one by Tech Support.
I am trying to get a list of orders from the Sales Shipment Line by Location Code, Document No., Item No, Shipment Date(aka orginal SO Shipment Date), and Ordered Quantity, The Go back to the Sales Shipment Line and get the (sum of the Quantity) and the Last Posting Date to see if an Order shipped Complete and On Time.
Thanks
Hi Todd -
For that, I would recommend creating a Grouping Report (rather than using the NL(Table) function)... unless of course you simply want to list out all your transactions and then use Excel pivot capabilities to do the heavy lifting.
Thanks for the feedback I did want to use excel to do the heavy lifting. I converted the table functions to a rows and used a last function to get the last posting and sum function to get total shipped for the SO. I then created a formula to get the dynamically get last used row "=LOOKUP(2,1/(NOT(ISBLANK(C:C))),ROW(C:C))" from the original base function rows. I used that value for the countifs and sumifs formulas like so "INDIRECT("sl!D5:D"&$R$5)". We are trying get an ontime delivery report for sales orders by location and the best way to do this is to call the Sales Shipment Line to get the base order data and then call sales shipment line to get the total shipped for an order item then call it again to get the Last posting date. Its messy but seems to work now.