HI
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.
thanks
Dave
Date
Votes
8 comments
-
Jet Reports Historic Posts Hi Dave,
Can you explain how exactly your report is not working for you? It seems to be sorting correctly for me, but I'm just looking at sample data so maybe there is something I'm missing.
Regards,
Hughes -
Jet Reports Historic Posts hi Hughes
on running the report on the Cronus data, for Sheet Sum Sorting I get the following incorrect data, as the 7,200 should be the last figure for location green.
GREEN
GREEN 44127914 7,200.00
GREEN 45858585 1,368,982.72
GREEN 47586622 1,337,115.97
the pivot table sorts fine:
Row Labels Sum of Amount
BLUE
30000 50660
GREEN
45858585 1368982.72
47586622 1337115.97
44127914 7200
SILVER
44127914 200
Below is the data I have in the Purchase Line table. it seems that NL sum is get the sum for each Vendor in the same vendor sequence as shown in the table:
Location Code Pay-to Vendor No. Amount
BLUE 30000 50660
GREEN 44127914 7200
GREEN 45858585 20800.86
GREEN 45858585 47510.82
GREEN 45858585 207792.2
GREEN 45858585 105519.5
GREEN 45858585 841904.8
GREEN 45858585 145454.54
GREEN 47586622 122940.95
GREEN 47586622 121175.67
GREEN 47586622 330135
GREEN 47586622 66027
GREEN 47586622 125806.7
GREEN 47586622 55079.85
GREEN 47586622 22009
GREEN 47586622 33529.35
GREEN 47586622 55079.85
GREEN 47586622 405332.6
SILVER 44127914 200
regards
Dave -
Jet Reports Historic Posts Hi Dave,
Did you already try to break up the code like this one:
http://community.jetreports.com/viewtopic.php?f=23&t=2109
I'm not sure if your quotes are working as they should. Your way looks the same but it might act a different. -
Jet Reports Historic Posts hi
thanks for the link, but that example works as the NL Rows is on the customer table whilst the NL Sum is on the Cust. Ledger Entry table. On the other hand, both my NL Rows and NL Sum are based on the same table Purchase Line. -
Jet Reports Historic Posts Hi Dave,
I've tried to solve this one but my headache is killing me now :)
The only thing I can see is that you've got your location code F7 quoted. I don't think this cell is being replicated because of the quotes.
Could you run your sheet without values and check the formula. I think your F7 doesn't change at all.
I have no sollution but I think it has to do with this
Michel -
Jet Reports Historic Posts tried the " for F7 but excel returns an error
-
Jet Reports Historic Posts Hi,
Aaah, I see the problem now. As Michel points out, it is that the F7 inside your sort by sum formula is quoted. So currently you have this:=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.""))","*")
The problem is that as this gets replicated down, you don't want it to always refer to F7. You want it to refer to the location code in each new row. So to do that you need to unquote it like this:=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.""))","*")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts thanks….that did the trick!! I had tried "F7" instead of """&F7&"""
I now need to take it a step further, as I want to report the highest amounts for each location. using "1" and "2" worked fine for the first & second but "3" is still reporting the same value as the second. I need to carry out some further tests.
Please sign in to leave a comment.