0

Top 10 Customers by Item

Jet Reports Historic Posts

September 11, 2013 14:02

None

Follow

Hello All,

I'm trying to find top 10 customers by item.
What I'm doing is filter items by characteristics (in Rows) and then (in columns) I want jet to spit out top 10 customers by each item.
Here is the formula I'm using to find top 10 Customers:
=NL("columns","Customer","No.","-=nl(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"",""05/01/13..08/31/13"",""Document Type"",""Sales Invoice|Sales Credit Memo"",""Item No."",$K9)","*","Limit=",10)

Problem is - If I hardcode item Code in the formula it works fine but if I type in cell reference like $K9 instead of Item code it's not working.

Any Suggestions..??

Shubhpreet

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts September 11, 2013 16:58

  Hi Shubhpreet,
  
  In order to use a cell reference that might change down rows like $K9, you would need to do it like this:
  
  =NL("columns","Customer","No.","-=nl(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Posting Date"",""05/01/13..08/31/13"",""Document Type"",""Sales Invoice|Sales Credit Memo"",""Item No."","""&$K9&""")","*","Limit=",10)
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts September 23, 2013 14:04

  Thankyou for the input. Here is what I have - Now I cannot get it to replicate across the columns. I get the top customer by item for the time period but I want the other 9 in descending order. Any ideas
  
  NL("columns","Customer","No.","Limit=","10","-=nl(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Item No."","""&$B10&""",""Posting Date"",""01/08/13..31/08/13"",""Document Type"",""Sales Invoice|Sales Credit Memo"")","<>0")

• 0
  
  Jet Reports Historic Posts September 26, 2013 12:44

  Hi,
  
  I'm guessing that most likely the problem is your <>0 filter. With calculated filters, you have to tell Jet what type of value to expect, so you may need something like this:
  
  =NL("columns","Customer","No.","Limit=","10","-=nl(""Sum"",""Value Entry"",""Sales Amount (Actual)"",""Source No."",NF(,""No.""),""Item No."","""&$B10&""",""Posting Date"",""01/08/13..31/08/13"",""Document Type"",""Sales Invoice|Sales Credit Memo"")","NUMBER&<>0")
  I'm guessing that they will replicate this way although you may not get the sort order that you want since this implies that most likely all the results of the sum are 0. Still, it's worth a try.
  
  Regards,
  Hughes