Hello,
I try to sort by sum but i don't know how to do it. I tried this (https://support.jetglobal.com/hc/en-us/articles/115006132828-Sort-by-Sum-or-count-with-Dynamics-NAV).
The sorting doesn't okay.
Someone can help me please ? I can send my file to watch the formula if it's necessary to understand.
Best Regards,
PEJ
in E7 : =NL("Lignes=5";"Famille article";"Code";"Code";"<>@@";$P$3;"*")
in J9: =NL("Somme";"Ecritures valeur";"Montant vente (réel)";"Code agence";I$6;"Date comptabilisation";periode_n;"Type écriture comptable article";"Vente";;;"Lien=";"Article";"N°";"=N° article";;;"Code famille";"@@"&$E8;"Code sous-famille";"@@"&$F8;"Code sous-sous-famille";"@@"&$G8)
in P3 : ="=NL(""Somme"";""Ecritures valeur"";""Montant vente (réel)"";""Code agence"";O$6;""Date comptabilisation"";periode_n;""Type écriture comptable article"";""Vente"";;;""Lien="";""Article"";""N°"";""=N° article"";;;""Code famille"";NF(;""Code""))"
I want to sort, in the column J the sales amount.
Hello Pierre-Elian.
J'ai faire mes études en Français il y a longtemp...
but it appears that you may have some tables and fields that I do not have in my standard sample Cronus database.
So, I will make (what I hope is) a simple example that still demonstrates the process.
Let's assume that I have:
- an NL(Rows) function what lists a code or number
- an NL(Sum) function that references that code/number AND other cells AND contains a LINK= filter.
Here is such an example:
In this case, my "code/number" is the No. field from the Customer table.
My NL(Sum) is accessing the Sales Amount (Actual) field in the Value Entry table, using a LINK=, and references cells D2, D3, D4, and E7.
When I run the report, it gives me my data, but not sorted by the total:
In order to sort by the total, I need to include that total INSIDE of my NL(Rows) function.
First, I make an exact copy of my NL(Sum) function and place it in another cell (in this case cell H2).
I need to modify that function so that I can use it as a filter field (called a "calculated field") in my NL(Rows) function.
Since I'm going to be using this function inside of my NL(Rows) in cell E7, I cannot reference cell E7 (Excel wouldn't know how to calculate that circular reference). Instead, I need to refer to the same No. field as is being examined by the NL(Rows) function.
From this:
=NL("Sum","Value Entry","Sales Amount (Actual)","Source Type","Customer","Source No.",E7,"Posting Date",$D$2,"Customer Group Code",$D$3,"Link=","Customer","No=","Source No.","Salesperson Code",$D$4)
to this:
=NL("Sum","Value Entry","Sales Amount (Actual)","Source Type","Customer","Source No.",NF(,"No."),"Posting Date",$D$2,"Customer Group Code",$D$3,"Link=","Customer","No=","Source No.","Salesperson Code",$D$4)
Next, I'll use the Quote button on the Jet ribbon to start preparing this function as a filter field:
="=NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source Type"",""Customer"",""Source No."",NF(,""No.""),""Posting Date"",$D$2,""Customer Group Code"",$D$3,""Link="",""Customer"",""No="",""Source No."",""Salesperson Code"",$D$4)"
Because all of my cell references are inside the first and last quotation marks, they are now just text... not references to cells.
Also, remember, that every filter and filter field, must have quote marks around them:
So, my next step is to put those additional quote marks to denote where text ends and cell references begin, and to ensure that there is an additional quote around each cell reference (along with the @@ technique, if any of those cell references can be blank).
From this:
="=NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source Type"",""Customer"",""Source No."",NF(,""No.""),""Posting Date"",$D$2,""Customer Group Code"",$D$3,""Link="",""Customer"",""No="",""Source No."",""Salesperson Code"",$D$4)"
to this:
="-=NL(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source Type"",""Customer"",""Source No."",NF(,""No.""),""Posting Date"",""@@"&$D$2&""",""Customer Group Code"",""@@"&$D$3&""",""Link="",""Customer"",""No="",""Source No."",""Salesperson Code"",""@@"&$D$4&""")"
Note that each cell reference require 3 quote marks and an ampersand symbol: " " @@ "& cell reference & " " "
Next, I want my list sorted from largest to smallest, so I will put a negative sign at the beginning of that text:
="-=NL(""Sum"",""Value Entry...
Finally, I need to add a reference to my new filter in my original NL(Rows) function:
Now, when I run my report, the results are sorted by the sum defined in cell H2:
I hope that gets you started in the right direction.
Thanks for your help Harry.
It was very complicated, indeed, Excel just accept 255 characters by formula. I have a lot of filter and argue in my formulas. So when I want to add some quotes, there is an error.
So i prefer to use a different method. I just make a sheet data and i make a pivot table.
It's really easy and faster than a report.
Thanks for your biiiiiiiig answer, i tried all the day to improve my report and i choose to create a pivot table.
Bonne journée :)
Pierre-Elian
Great solution!