Sorting Sum


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.



Please sign in to leave a comment.