Hello,
I'm trying to do a Top __ Customers by Salesrep report. My main rows formula is this:
=NL("Rows","Customer",,"Salesperson Code",$C$4,"No.",$C$5,$P8,"*","Limit=",$C$6)
Cell P8 contains my sum formula which is:
="-=NL(""Sum"",""Customer"",""Sales (LCY)"",""Date Filter"",$C$2,""No."",NF(,""No.""))-NL(""Sum"",""Customer"",""Sales (LCY)"",""Date Filter"",$C$3,""No."",NF(,""No.""))"
I'm taking the difference of the sum of sales between 2 date ranges and I want it to sort by that difference and only return the top 5. When I put in 5 for my limit it doesn't sort it. When I put in * for the limit it correctly sorts the results by the sum formula. Anyone know why it doesn't work with the limit?
Date
Votes
8 comments
-
Jet Reports Historic Posts Instead of using separate NL(Sum) function, you can do this with a nested NF calculation inside your NL(Rows):
=NL("Rows";"18 Customer";;"-=NF(;""62 Sales (LCY)"";""55 Date Filter"";""1-1-2009..31-12-2009"")-NF(;""62 Sales (LCY)"";""55 Date Filter"";""1-1-2008..31-12-2008"")";"<>0";"Limit=";"5")
Of course you need to replace the date filters by yours and add your sales person filter. -
Jet Reports Historic Posts Ah sorry.. I had a + sort in the main rows function that was overriding it.. :oops:
-
Jet Reports Historic Posts On a related note, can't seem to get this working to sort by margin %
I have the two functions for profit and sales:
-=NL("Sum","Customer","Sales (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Sales (LCY)","Date Filter",$C$3,"No.",NF(,"No."))
-=NL("Sum","Customer","Profit (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Profit (LCY)","Date Filter",$C$3,"No.",NF(,"No."))
I tried to do one big function of
-=(NL("Sum","Customer","Sales (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Sales (LCY)","Date Filter",$C$3,"No.",NF(,"No.")))/=(NL("Sum","Customer","Profit (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Profit (LCY)","Date Filter",$C$3,"No.",NF(,"No.")))
but it said it couldn't evaluate the function. Is this possible some how? The results would be something like 50.32% and I want to sort on largest to smallest of that -
Jet Reports Historic Posts It looks like you've just got an extra = sign in the middle of your formula (right before the 3rd NL(Sum) function), which would cause Excel to be unable to evaluate it. Try removing it.
-=(NL("Sum","Customer","Sales (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Sales (LCY)","Date Filter",$C$3,"No.",NF(,"No.")))/=(NL("Sum","Customer","Profit (LCY)","Date Filter",$C$2,"No.",NF(,"No."))-NL("Sum","Customer","Profit (LCY)","Date Filter",$C$3,"No.",NF(,"No.")))
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Thanks for that catch Hughes. I removed the extra =, but I'm still getting the same error. Maybe it's just to many functions
-
Jet Reports Historic Posts If you take out the "No.",NF(,"No.") from all of the functions it should work.
=(NL("Sum","Customer","Sales (LCY)","Date Filter",$D$3)-NL("Sum","Customer","Sales (LCY)","Date Filter",$D$4))/(NL("Sum","Customer","Profit (LCY)","Date Filter",$D$3)-NL("Sum","Customer","Profit (LCY)","Date Filter",$D$4))
That filter is a bit circular and looks to be the root of the problem. When I took those out, I then got a #DIV/0 error which is understandable since I don't have any data and am dividing by zero.
Does that give you the correct result? -
Jet Reports Historic Posts The formula works technically but it's not what I'm after. The "No.",NF(,"No.") is what ties the sum filter back to the particular customer record jet reports is currently on. Without it it's just taking the sum of all customer sales
-
Jet Reports Historic Posts When I look at that, it would seem that you would need an external reference to get the Customer Number to filter on. As at it stands, "No.",NF(,"No.") has nothing to reference which is why, at least in my test environment, it says it is an empty filter set.
My own personal preference would be to create each of the values you want in separate cells so I could quickly see the data going into the larger function. I also think it makes it easier to debug because you can then quickly see where there is an error instead of trying to edit a massively complex Jet function. Perhaps even breaking it down like that will help you build one larger function as you get each part to work individually.
Due to the complexity of your formula and having limited access to exactly what you are trying to accomplish and your data, I would suggest contacting a designer who can help you get exactly what you want out of this function.
Please sign in to leave a comment.