I need to sort the result of the sum on the Purchase Line table in Navision.
As a simple example I need to have a total per Location per Pay-to-Vendor, sorting by Location and then the Vendor with the highest amount. I have created an NL Function to list the Location Codes then includes the below function to Sum the Amount for each Pay-to Vendor, sorting the highest amount. This is not always calculating the correct result.
=NL("Rows","Purchase Line","Pay-to Vendor No.","Location Code",$F7,"-=NL(""Sum"",""Purchase Line"",""Amount"",""Location Code"",F7,""Pay-to Vendor No."",Nf(,""Pay-to Vendor No.""))","*")
I know that this can be achieved by using the Pivot table, but cannot use it as need to include other fields to this report. attached is a copy of the Test report I've created…… the above formula is in sheet names Sum sorting.
any ideas on how to sort it are welcome.