0

Sorting Count

Job Wintjes

August 15, 2024 14:24

None

Follow

Hello,

I would like to count how many times a product group is used in all sales lines. Afterwards sort by high to low and only show top 2.

nav table "product group"
Code
TABLE
CHAIR
BENCH

nav table "sales line"
SO.                    PRODUCT GROUP CODE
sales order1      CHAIR
sales order2      CHAIR
sales order3      BENCH

I would like to achieve:
#1  CHAIR
#2  BENCH

not working for me:
=NL("Rows";"Product Group";"Code";"DataSource=";"Navision 2009 Live";"Company=";"Company A";"-=NL(""Count"";""Sales Line"";;""Product Group Code"";NF(;""Code""))";"*")

Can someone help me in the right direction?

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Job Wintjes August 16, 2024 11:28

  After many tries, I found a solution:
  
  I am not sure but it looks like error was caused by a product group code that was empty
  
  For the person who might be wondering:
  
  =NL("Rows";"Product Group";;"DataSource=";"Navision 2009 Live";"Company=";"Copmany A";"Code";"<>''";"-=NL(""Count"";""Sales Line"";;""DataSource="";""Navision 2009 Live"";""Company="";""Company A"";""Product Group Code"";""<>''"";""Product Group Code"";NF(;""Code""))";"*";"Limit=";2)

  Edited by Job Wintjes August 16, 2024 11:28