Most effective way to teach sort by SUM

Put your best Excel related tips and tricks here.
Forum rules
This is the place to put all of your Excel Tips & Tricks.
Tarek
Posts: 63
Joined: Wed Jun 24, 2009 2:41 pm

Most effective way to teach sort by SUM

Postby Tarek » Thu Mar 01, 2012 11:31 am

What's the most effective way you've found to teach and show the concept of sorting by SUM in Jet Reports ?
Best Regards,
Tarek Demiati

Jet Reports Consultant

E-mail : TAREK AT SunSoftWorks DOT com

User avatar
fhilton
Posts: 1457
Joined: Fri Dec 14, 2007 4:43 pm

Re: Most effective way to teach sort by SUM

Postby fhilton » Fri Mar 02, 2012 10:28 am

Hi Tarek,

Sort by sum is definitely complicated. Of course the easiest thing for most people would be to either buy Jet Enterprise or create a flow field in NAV with the sum they need and whatever flow filter fields they need to filter by. So assuming you still want to teach them to do a sort by sum in Jet (and assuming we're talking about NAV here since it's different in Universal), here are the steps I would use:

1. Write your formula to retrieve customer numbers in cell E4 like this:

Code: Select all

=NL("Rows","Customer","No.")
2. Write your sum formula in F4 it like this:

Code: Select all

=NL("Sum","Cust. Ledger Entry","Amount","Customer No.",E4)
3. Now change your formula in F4 by replacing the cell reference with to E4 with NF(,"No.") which will retrieve the No. field from the current customer record. So the formula looks like this (and current returns #VALUE):

Code: Select all

=NL("Sum","Cust. Ledger Entry","Amount","Customer No.",NF(,"No."))
4. Make sure your NL(Sum) formula cell is selected, and in the Jet ribbon, select Tools -> Quote. Now your formula looks like this:

Code: Select all

="=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""))"
5. Add a + or - (depending on whether you want ascending or descending sort) to the beginning of the formula right before the 2nd = sign like this:

Code: Select all

="-=NL(""Sum"",""Cust. Ledger Entry"",""Amount"",""Customer No."",NF(,""No.""))"
6. Change your NL(Rows) formula in E4 to reference the NL(Sum) formula like this:

Code: Select all

=NL("Rows","Customer","No.",F4,"*")
That's it! Now you have a list of customers sorted in descending order by the sum of their customer ledger entries. Of course, you could just sort by the Balance flow field on the Customer table, which will do the exact same thing. When you really need the sort by sum is if you need to filter the Cust. Ledger Entry table by a field that's not exposed as a flow filter field on the Customer table. Does this help?

Regards,
Hughes

ferdinand
Posts: 11
Joined: Wed Feb 08, 2012 4:22 pm

Re: Most effective way to teach sort by SUM

Postby ferdinand » Mon Sep 24, 2012 4:28 pm

Hopefully it is okay to ask a question here since it is regarding this tip. This sample is not working for me but I also cannot seem to find the quote feature. My Excel Jet Ribbon has Tools but there are no options to select anything. Could this be where things are going array and does anyone have an idea why my tools would be blank? TIA!

User avatar
fhilton
Posts: 1457
Joined: Fri Dec 14, 2007 4:43 pm

Re: Most effective way to teach sort by SUM

Postby fhilton » Mon Sep 24, 2012 4:40 pm

Hi,

When you click the Tools dropdown button, it should expand with a menu of 3 items; Quote, Quote Global, and Unquote. If this is not the case, and you click the button and get nothing, then I would create a case with Jet Support to discuss the issue.

Regards,
Hughes

ferdinand
Posts: 11
Joined: Wed Feb 08, 2012 4:22 pm

Re: Most effective way to teach sort by SUM

Postby ferdinand » Fri Sep 28, 2012 4:20 pm

Thanks Hughes! Funnily enough at the time I was getting nothing, but when I go to create a new report and select the Tools button I get the three options! Strange! Must have been something strange with the report I was in. Thanks for your suggestion!

slangley
Posts: 4
Joined: Mon Sep 22, 2014 2:38 am

Re: Most effective way to teach sort by SUM

Postby slangley » Tue Oct 14, 2014 5:04 am

Hi,

I am trying to apply the above formula to another 2 tables within Jet.
For my rows I have the following:
=NL("Rows","Customer","No",H7,"*")

and then in H7 I have the following:
="-=NL(""Sum"",""Sales Analysis History"",""Nett - LTA Amount (LCY)"",""Sell to Customer No"",NF(,""No.""))"

when I try to run the report I get the following error in the attachment.

Can anyone help as I cannot see what I have done wrong?

Thanks,
Shauna

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Most effective way to teach sort by SUM

Postby The Kloser » Tue Oct 14, 2014 12:51 pm

Hi Shauna,

I didn't see any attachment to see the error message that you received. If you are still having an issue, could you please send it in?

slangley
Posts: 4
Joined: Mon Sep 22, 2014 2:38 am

Re: Most effective way to teach sort by SUM

Postby slangley » Tue Oct 14, 2014 12:53 pm

Error attached now I hope
Attachments
Jet Error.docx
(26.26 KiB) Downloaded 126 times

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Most effective way to teach sort by SUM

Postby The Kloser » Tue Oct 14, 2014 1:09 pm

Thanks for sending it in. Since the whole string is coming back as an error, I looked closer at the double quoted NL(Sum) function. I think the issue has to do with a field name inside. You currently have:


="-=NL(""Sum"",""Sales Analysis History"",""Nett - LTA Amount (LCY)"",""Sell to Customer No"",NF(,""No.""))"

I think the problem is that the field name might be wrong for the sell to customer no. I don't have the Sales Analysis History table in my NAV system but in other tables, this field is called "Sell-to Customer No." Try adding the "-" after Sell and the "." after No.

Does it work now?

slangley
Posts: 4
Joined: Mon Sep 22, 2014 2:38 am

Re: Most effective way to teach sort by SUM

Postby slangley » Thu Oct 16, 2014 7:59 am

This is the way the field is defined in the data warehouse
Attachments
IMG_16102014_155817.png

User avatar
The Kloser
Posts: 159
Joined: Mon Jan 07, 2008 5:08 pm

Re: Most effective way to teach sort by SUM

Postby The Kloser » Thu Oct 16, 2014 9:58 am

Hi Shauna,

This is the issue. As Hughes mentions in his post, this syntax is only available for NAV data sources. Since this is a connection to your DWH, this will not work - hence your error.

To sort on a sum in a Universal data source, you can use this syntax instead of the other 2 functions:

=NL("Rows","Sales Analysis History","Sell to Customer No","-Sum(Nett - LTA Amount (LCY))")

This function will list the Sell to Customer No's in descending order based on the sum of "Nett - LTA Amount (LCY)"

Does this make sense?

slangley
Posts: 4
Joined: Mon Sep 22, 2014 2:38 am

Re: Most effective way to teach sort by SUM

Postby slangley » Fri Oct 17, 2014 3:33 am

Yes that does make sense and thank you very much.

I was also wondering how you would sort if your rows were based on the master table (i.e. Customer)
So in another report I have the rows as

=NL("Rows","Customer","No","Category",E5,"Category Master",$C$4,"Link=","Sales Analysis History","Sell to Customer No","=No","Shipment Date",$B$9)
and this lists the customers that are contained in the Sales Analysis History for a certain range and category.

The column I want to sort on is as follows
=NL("Sum","Sales Analysis History","Nett - LTA Amount (LCY)","Shipment Date",H$4,"Sell to Customer No",$E7)

So how do you sort the customers from the customer table based on the sum of a field in another table.

Thanks again,
Shauna

jwittek
Posts: 2
Joined: Thu Jul 14, 2016 9:08 am

Re: Most effective way to teach sort by SUM

Postby jwittek » Thu Jul 14, 2016 9:51 am

I'm trying to follow this, but I don't see a Tools Dropdown list. Attached is what my Jet Ribbon looks like.
Attachments
Capture.JPG

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 443
Joined: Wed May 26, 2010 3:40 pm

Re: Most effective way to teach sort by SUM

Postby HPDeskJet » Mon Jul 18, 2016 7:10 am

Hi -

The Jet ribbon has undergone a few changes since this topic was originally written. It now includes the ability to turn various buttons on/off.

In your Application Settings, select the Jet Ribbon section and then ensure that the Quote button is checked:
app settings.png
That button will then be available on the ribbon.
-HP
_______________________
Jet 2015 Certified Trainer


Return to “Excel Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 1 guest