Good morning
I am doing the following formulas in Jet. I have territories and customers in each territory. I want to sort the territories by Sales Amount and inside of each territory sort the customers of it by Sales Amount as well.
I am trying different things but I don't understand what is wrong.
Hello Sebastian -
When you state that you "want to sort the territories by Sales Amount", I assume you want to sort by the SUM of all transactions in that territory.
Further assuming that you are using Dynamics NAV (as it appears you are), you will want to review the Sort By Sum article. This topic is a bit advanced, but the article provides step-by-step instructions (both text and video).
Hi Harry, Thanks for your answer.
Yes that is right. I want to sort it by the sum of the field "Sales sell-to": SUM of all transactions.
I already saw Sort By SUM but the things is that I am using Rows and Rows=2, and I don't think that this option will be possible. Or yes ?
I Appreciate your help,
Hello Sebastian -
Yes, you can perform a sort on both NL functions.
In your NL(Rows=2), you would apply the Sort By Sum technique with the Sales Sell-to field. In your next NL(Rows) function, you would simply sort by the field (not the sum).
Since my sample CRONUS database does not have a Sales sell-to field, I've put together a simple example using the Sales ($) field.
When I run my report, the territories are sorted (descending) by the total of all sales in the territory, and the individuals customers are sorted (descending) by the values of their Sales ($) field...
Harry
Thanks again for all your help.
I am trying to do it exactly as you did. But the point is that I need to filter it by Company and Dates. My Apologies but I don't know what I am doing wrong. Can you have a look ?
Hello Sebastian -
The syntax gets a little more challenging when you need to include cell references in your embedded NL(Sum) function.
The results would look something like this:
=NL("Rows=2","Customer","Territory Code","Territory Code","<>@@","-=NL(""Sum"",""Customer"",""Sales ($)"",""Territory Code"",NF(,""Territory Code""),""Date Filter"","""&$C$3&""",""Company="","""&$C$2&""")","*")
Another thing to keep in mind is that row 1 and column A are reserved for Jet keywords... you'll want to ensure that you do not place any of your own data/functions/formulas in row 1 or column A.
Hi Harry
Once again, I appreciate a lot your help.
Yes I know about the row 1 and column A, I was just writing the example.
Have you tried the last formula that you sent ?. I am trying but is still no working.
Sorry for bother a lot and thanks for your answer.
Hello Sebastian -
Yes, I am able to use that exact function as part of this report:
with these results:
I know that field #62 [Sales (LCY)] is a flow field... I do not know about the field you are using.
If you duplicate my report and use these fields:
=NL("Rows=2","18 Customer","15 Territory Code","15 Territory Code","<>@@","-=NL(""Sum"",""18 Customer"",""62 Sales ($)"",""15 Territory Code"",NF(,""15 Territory Code""),""55 Date Filter"",""""&$C$3&"""",""Company="",""""&$C$2&"""")","*")
and run the report, what type of results do you obtain?