1

NL Sum as filter in a NL filter

Pablo Álvarez

January 12, 2018 13:37

None

Follow

I need your help on my formula.  I need a NL filter with all dealers who bought more than 1000€ last year.

So my try:

=NL("Filter";"Customer";"Nº";NL("Sum";"Cust. Ledger Entry";"Amount";"Posting Date";"010117..311217";"Salesman code";1;"Document type";"Invoice";"Customer Nº";NF(;"Nº"));">0")

This is not working, I think the  problem is on the ;NF(;"Nº"))...

What do you think??

PS(I'm spanish so sorry if any field has a "weird" name, I tried to translated but I'm not sure that is the right name on NAV versions)

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Pablo Álvarez January 15, 2018 07:54

  Hello,

  I have rewritten the formula as follows

  =NL("Filter";"Customer";"Nº";"Salesman code";1;"=NL(""Sum"";""Cust. Ledger Entry"";""Amount"";""Posting Date"";""010117..311217"";""Document type"";""Invoice"";""Customer Nº"";NF(;""Nº""))";">1000")

  It seems everything is working well but it is not filtering as I would like.  In fact I don't know what is filtering but something is being filtered.

  What do you think?

  Thank you for your help!

• 0
  
  Harry Lewis January 16, 2018 15:30

  Hello Pablo -

  Without details, my guess is that both the "1000" and the results of your NL(Sum) are being interpreted as text.

  To force a numeric comparison, try this:

  =NL("Filter";"Customer";"Nº";"Salesman code";1;"=NL(""Sum"";""Cust. Ledger Entry"";""Amount"";""Posting Date"";""010117..311217"";""Document type"";""Invoice"";""Customer Nº"";NF(;""Nº""))";"NUMBER&>1000")