I am using jet 2009 and Nav 5.1. I read other posts on sorting by sum and created this formula which is working fine except it is duplicating the rows for ranking. I have attached the sample output.
Here is my NL function:
=NL("rows","Cust. Ledger Entry","Customer No.","-=NL(""sum"",""Cust. Ledger Entry"",""Amount ($)"",""Customer No."",NF(,""Customer No.""),""Posting Date"","""&$E$6&""",""Document Type"","""&$E$7&""",""Global Dimension 1 Code"","""&$E$8&""",""Global Dimension 2 Code"","""&$E$9&""")","*","Posting Date",$E$6,"Limit=",$E$5)
I wanted top 5 customers by sales in desceding value. It gives me repeat rows (25) for these five customers.
Cell E5 formula is: =IF(ISNUMBER(Options!D4),Options!D4,5)
Rank cell formula is: =NL("Rows","Integer","Number","Number","1.."&$E$5)
Can any one please help me fix the problem that causing this issue.
Date
Votes
2 comments
-
Jet Reports Historic Posts What is the rank cell formula being used for? It doesn't seem necessary based on what you're trying to do, and I believe that it is what is causing the problem. If you want the top 5 customers sorted descending by sales, that is what the first formula you posted will give you. However, if you put another NL("Rows") formula on the same row (i.e. the rank formula), you actually get permutations of the results (5*5=25).
Hope this helps.
Regards, -
Jet Reports Historic Posts Here is an elegant way to show sales reps side-by-side and listing their top customers in descending order.
Please sign in to leave a comment.