Hi
I am fairly new to Jet but have found some information on here regarding Sort on Sum which was very helpful but I am still having trouble trying to get mine to work.
I am trying to output a list of customers (by finding all the customers for a site with any entries in the invoice ledger) and rank them by total invoice value for the period (in this case its a year). The rows part of the formula is alos set to unique. The formula I have is
=NL("Rows","Invoice Ledger Entry","Source No.","+=NL(""Sum"",""Invoice Ledger Entry"",""Amount"",""Posting Date"","""&Filters!$E$18&""",""Global Dimension 1 Code"","""&Filters!$E$5&""",""Global Dimension 2 Code"","""&Filters!$E$6&""",""Source No."",NF(,""Source No.""),""Source Type"",""Customer"",""Amount"","">0"")","*","Global Dimension 1 Code",Filters!$E$5,"Global Dimension 2 Code",Filters!$E$6,"Posting Date",Filters!$E$18,"Source Type","Customer")
Where:
Filters!$E$18 is a date range in the format dd/mm/yyyy…dd/mm/yyyy
Filters!$E$5 is our company code (which is just MNB), global dim 1
Filters!$E$6 is our site code (which is just LEE), global dim 2
If I need to post more info let me know, any help would be much appreciated.
Dave
Date
Votes
1 comment
-
Jet Reports Historic Posts Sorry, I should have alos pointed out this is the error I get after it running for five minutes or so.
"The formula on sheet "Sheet 1" in address $B$9 cannot be used with NL(Rows), NL(Columns) or NL(Sheets). Your report may exceed Excel's row and column limits, your formula may be too long, or your formula may contain both nested functions and named ranges. Excel has a worksheet limit of 65536 rows and 256 columns. Both you cannot use both nested functions and named ranges in the same formula"
It ran OK without the sum function being added, bringing back 50 or socustomer.
Please sign in to leave a comment.