Hi all,
First time poster having browsed occassionally!
I have an issue in that I've been asked to provide a report that provides top ten Vendors on outstanding balance at a given date.
I've knocked something together than picks up all unique Vendors from the Vendor Ledger entry table. I then perform an NL("Sum") on the Detailed Vendor Ledger Entry table filtering on Vendor No. and where Posting date <= chosen date. This returns the correct figures.
How do I limit the result set to top 10 on the Total figure?
Regards,
Q
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Hughes,
Thanks very much for your prompt response. You're not kidding that it is complicated! It does exactly what required although needed a little bit of tweaking (I didn't mention that I was pointing at a company other than default).
My final NL ended up looking like this (C4=Company, D4=Posting date)=NL("Rows","Vendor",,"Limit=","10",0,$C$4,"+=NL(""Sum"",""Detailed Vendor Ledg. Entry"",""Amount"",""Vendor No."",NF(,""No.""),""Posting Date"",""<=""&text($D$4,""dd/mm/yyyy""),0,"""&$C$4&""")","*",0,""&$C$4&"")
As the sum of the amount field is negative and had to change the sort order on the sum from descending to ascending.
Thanks again for you response - much appreciated! :D
Regards,
Q -
Jet Reports Historic Posts Hi Q,
This is actually slightly complicated, but it looks something like this:
=NL("Rows","Vendor",,"Limit=","10","-=NL(""Sum"",""Detailed Vendor Ledg. Entry"",""Amount"",""Vendor No."",NF(,""No.""),""Posting Date"","""&$C$2&""")","*")
I'm assuming here that your posting date filter is in cell C2. The filter field which starts with an = gets passed through Excel's calculation engine and gets evaluated for each record in the Vendor table. The - sign at the beginning of that filter field causes it to sort in descending order (thus the largest values first). The NF function with no record key gets the value from the current record of the Vendor table which is how the sum formula is liked to the rows formula. Then you use Limit= to get only the top 10. This is called a sort by sum and it is fairly slow since we have to do the sum for each record on the main table, but it should give you the results you are looking for.
Regards,
Hughes
Please sign in to leave a comment.