Hi there,
I searched the forum for sorting by sum. Lots of info but I guess I'm not at the level to understand it, yet. Here's what I have:
Cell
C4: =NL("Rows","Item","Customer Group","No.",NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale"))
E4: =-NL("Sum","Item Ledger Entry","Quantity","Entry Type","Sale","Posting Date",HIDDEN!$F$10,"Item No.",NL("filter","Item","No.","Customer Group",$C4))
F4: =-NL("Sum","Item Ledger Entry","Quantity","Entry Type","Sale","Posting Date",HIDDEN!$E$10,"Item No.",NL("filter","Item","No.","Customer Group",$C4))
G4: =-NL("Sum","Item Ledger Entry","Quantity","Entry Type","Sale","Posting Date",HIDDEN!$D$10,"Item No.",NL("filter","Item","No.","Customer Group",$C4))
I'm pulling the sum of all sales from 2009 through this year so i can put them on a chart. E4 is 2009 and so on. What I need to show on the chart is the top 5 customers with the most sales. As it sits, the report runs in ascending order by customer group. I'd like for it to run in descending order by the sum for each year or by the sum of all 3 years together. This way, I'll be able to limit the records in a query to 5 customers in order from greatest amount of sales to least. I haven't a clue on how to do this with what I have now. Ideas?
Sorry if this has been covered before.
Thanks,
DR
Date
Votes
19 comments
-
Jet Reports Historic Posts Official comment Hi DR,
I think what you want is something like this:
=NL("Rows","Item","Customer Group","No.",NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale"),"-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")","*")
I don't know what date ranges you have on your hidden sheet for 2009, 2010, and 2011, so I just made a date range that included all 3 years and you can adjust that accordingly. Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Wow!
Worked like a charm!
Thanks, Hughes! -
Jet Reports Historic Posts DR,
That's great! I'm glad I got it right; these can be tricky sometimes. Since you're interested, I will try to explain a little bit how this works. Sorry, this can get a bit technical. Basically, when Jet encounters a filter field which starts with the = (at least after we've stripped off the + or - for sorting), it considers that a calculated filter field. Then when we are spinning through database records from the primary table (the Item table in your case), for each record we tell Excel to calculate the contents of that filter. Therefore the filter field must be a valid Excel function. In this case, it is a Jet NL function, but it could be any Excel function.
Before telling Excel to calculate the filter field, we save the current record we're using. Then when we tell Excel to calculate the filter field, Excel comes back into the Jet code, first to ask for the value of the embedded NF function, then for the NL(Sum). When we encounter an NF function with no record key, we use the current record which we saved from the primary function, so the NF returns the value of the No. field from the Item table for the current record in the Item table. This is how the NL(Sum) on the Item Ledger Entry is actually linked with each record from the Item table. Then we use whatever value comes back from the calculated field to sort and/or filter the values from the primary table.
The reason you have to use "" is that you need to properly quote the Excel function in the calculated filter field. Since the filter field argument is already inside quotes, specifying a single " character would end the argument and confuse Excel. So the way to get a quote character inside an already quoted string is to use 2 quote characters. You might be able to see this more easily if you actually remove the filter field and put it in its own cell like this:
="-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")"
This is an Excel formula just like ="" with the value inside the quotes being another Excel formula. Does this make sense?
Regards,
Hughes -
Jet Reports Historic Posts That interesting and very informative. Thank you.
Now, one last thing: I'm getting an error box when adding the "Limit=" into this formula. I've entered it as follows:
=NL("Rows","Item","Customer Group","No.",NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale","Limit=","5"),"-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")","*")
=NL("Rows","Item","Customer Group","No.",NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale"),"Limit=","5","-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")","*")
=NL("Rows","Item","Customer Group","No.",NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale"),"-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")","*","Limit=","5")
The error box reads:
"The formula on sheet "Sheet3" in address $C$4 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"
Am I able to enter this filter into this formula? -
Jet Reports Historic Posts You've probably actually made the formula too long for Excel to handle. You should either put the NL(Filter) into its own cell and reference it or put the sort by sum filter field in its own cell and reference it. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Again, worked like a charm.
I ended up pulling the NL filter out of the formula, placing it in an adjacent cell and then referencing it followed by the "Limit=".
C4:
=NL("Rows","Item","Customer Group","No.",$B$4,"Limit=","5","-=-NL(""Sum"",""Item Ledger Entry"",""Quantity"",""Item No."",NF(,""No.""),""Entry Type"",""Sale"",""Posting Date"",""1/1/2009..12/31/2011"")","*")
Where $B$4: =NL("Filter","Item Ledger Entry","Item No.","Entry Type","Sale")
I hope this helps anyone that needs it.
Appreciate the help, Hughes! -
Jet Reports Historic Posts Thank you so much "fhilton" for your help on this filter, and thank you Jet Reports Community for putting these forums together. By using a search, I came across this post and it gave me exactly what I was looking for, and there is no way I could've figured this out without this post. I've been working on this for a couple days now, but finally got my answer. I was trying to run a Jet Report showing the Top 20 Customers per SalesRep based on YTD Sales. (We have NAV 2009 and you can run that easily in Navision, but I needed it in Jet so I can add additional reporting) Anyways, through your suggestions above, I came up with the following formula and it works great! Thank you "Dramos" for asking the question, and "fhilton" for answering.
Here is my formula:
nl("Rows","Customer","No.","Salesperson Code",SALESMAN,"-=nl(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""),""Posting Date"",""01/01/11..12/31/11"",""Document Type"",""Invoice|Credit Memo"")","*","Limit=",20)
Thanks,
Scott Sloan
Hammond, IN -
Jet Reports Historic Posts Hi,
I am trying to do something similar but I haven't had any luck getting it to work using your examples.
I am using Jet 2010 with NAV 5.
I am wanting to filter an A/R report to only show me the top 10 or 20 accounts with an aging balance. I currently have two groupings: First grouping is by Customer table, second is by Cust. Ledger Entry Table.
here is the code that I have in each:
=NL("Rows=5","Customer",,"No.",NL("Filter","Cust. Ledger Entry","Customer No.","Global Dimension 1 Code",$I$10),"company=",$I$7,"Date Filter",$I$8,"No.",$I$9,"Limit=",$I$16,"Global Dimension 2 Code",$I$11,"-=-NL(""Sum"",Cust. Ledger Entry"",""Remaning Amount"",""Customer No."",NF(,""Customer No.""),E25)","*")
=NL("Rows","Cust. Ledger Entry",,"Customer No.","@@"&$E26,"4 Posting Date",$D$21,"76 Date Filter",$D$21,"Remaining Amount",">0","Global Dimension 1 Code",$I$10,"Global Dimension 2 Code",$I$11,"Company=",$I$7,"Gen. Bus. Posting Group",$I$13,"Customer Posting Group",$I$14)
any assistance would be GREATLY apprecieated.
Thanks in advance,
Gustavo :cry: -
Jet Reports Historic Posts Hi Gustavo,
I'm looking at your sort by sum function and noticing that it has the wrong number of arguments. What is in cell E25? Is that formula itself in cell E25? The NF function with the blank record key is what will get you the current customer number from the parent table, so there is no need to reference the current cell.
Also, you specify NF(,""Customer No."") but this NF function is referring to the parent table (Customer) and there is no "Customer No." field on the Customer table, just a "No." field. Does this make sense? There are also a couple formatting errors like missing quotes before Cust. Ledger Entry and an extra minus sign at the beginning of the formula. The formula should probably look more like this:=NL("Rows=5","Customer",,"No.",NL("Filter","Cust. Ledger Entry","Customer No.","Global Dimension 1 Code",$I$10),"company=",$I$7,"Date Filter",$I$8,"No.",$I$9,"Limit=",$I$16,"Global Dimension 2 Code",$I$11,"-=NL(""Sum"",""Cust. Ledger Entry"",""Remaning Amount"",""Customer No."",NF(,""No.""))","*")
Does that work better?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thanks for responding. Yeah my code was pretty butchered up. I think your code wil work, but I can't do an NF function off of it. Should I be able to? -
Jet Reports Historic Posts I would assume that you could do an NF function off it since the field argument is empty. Of course things like sort by sum won't be evaluated until you run the report. And if a sort by sum doesn't work then most likely your NL function will just return blank (which cause any NF functions to also return blank). What is the problem you're having with your NF functions?
Regards,
Hughes -
Jet Reports Historic Posts I'm glad you said that, I didnt try to run the report. I was just noticing that the key was still blank, but after running it the key showed up. I also noticed that you left off the "i" in "remaining balance," on the code you provided.
After I fixed that it worked great!
I am eternally gratefull for your efforts. I could not have done this without this community or support by guys like you.
Thank you!
V/R,
Gustavo
:mrgreen: :mrgreen: -
Jet Reports Historic Posts Sorry to beat a dead horse, but I've been racking my brain all morning trying to replicate this solution for a simple "top 10" report. I've basically got a single table ("G/L Entry") with department (called "Global Dimension 1 Code"), posting date, g/l account no., description and amounts all on it. I just want to be able to run a report with variables entered for department, date, g/l account that would display the top 10 totals with corresponding description. What I've got now is:
=NL("Rows","G/L Entry","Description",-NL("Sum","G/L Entry","Amount","Global Dimension 1 Code",B4,"G/L Account No.",C4,"Posting Date",D2,"Limit=",10),"*") but it's returning #value! error.
Do I need two separate formulas? One for the description and a separate one just for the subtotals?
Thanks and again apologies for the repetitive question. -
Jet Reports Historic Posts Hi Bob,
I think you missed the point slightly. With a sort by sum, the NL(Sum) formula is quoted so Excel does not treat it as a regular formula. It's just a string to Excel since it's quoted. It also has to have an equals sign on the front in order for Jet to interpret it as a calculated filter field. Then Jet can take that string for each record returned and have Excel evaluate it to get the sum to sort that record by. You also need a nested NF function so that Jet knows how to link the inner NL(Sum) function with the current record from the outer function. You also probably need to apply your filters at both function levels. Finally, your limit= would go at the top function level since you want to return the top 10 records, not sum only the top 10 records.
In your case, it might look something like this:=NL("Rows","G/L Entry","Description","-=NL(""Sum"",""G/L Entry"",""Amount"",""Global Dimension 1 Code"","""&B4&""",""G/L Account No."","""&C4&""",""Posting Date"","""&D2&""",""Description"",NF(,""Description""))","*","Limit=",10,"Global Dimension 1 Code",B4,"G/L Account No.",C4,"Posting Date",D2)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts unfortunately still getting the error. I checked that my variable cells are working because a simple NL "sum" function returns the correct department total for the time period.
-
Jet Reports Historic Posts What error are you getting exactly?
Regards,
Hughes -
Jet Reports Historic Posts when I refresh the report I get a popup error that says "invalid filter [name of first description item on list]".
-
Jet Reports Historic Posts looks like it didn't like the "description" field for some reason. I changed it to "client" and now it's working with an NL(sum) function getting my $ in the adjoining cell. Thank you so much for your help.
now I'm running into another problem though and not sure if this is just an excel/computer overload issue: I'm trying to set up two side by side top 10 lists for different date ranges so I can do variance analysis. It works fine with just the one top 10 list but with the second one it takes a LONG time to compute and then I get 10 repeat sets of my top 10 list in one row and 10 sets of 10 of each client in the other. anyway to clean this up so it runs properly? seems like I'm not asking too much of excel but maybe I am…
thanks again for your help so far. -
Jet Reports Historic Posts Hi Bob,
You can't put 2 NL(Rows) replicators on the same row and get the results you are looking for. What happens is that for each row that the first NL(Rows) function creates, the 2nd NL(Rows) function will create all of its rows. This is because the NL(Rows) function makes a copy of the entire row, including the other NL(Rows) function. So first the left-most NL(Rows) function gets expanded and that makes 10 rows with 10 copies of your 2nd NL(Rows) function which then all get expanded.
Basically Jet is not a good tool for doing something like side-by-side replication of 2 independent lists because NL(Rows) always copies the entire row. There are some sort of "clever" tricks you can do to try and make this work, but it's really not a great idea. It's better to just move your 2nd top 10 list below the first one or to another sheet. Does that make sense?
Regards,
Hughes
Please sign in to leave a comment.