0

Filtering balances in credit

Barry Thompson

January 13, 2023 11:25

None

Follow

Morning,

I am using an NL filter to filter out any customer records where the balance of their invoices, before a filtered due date, is greater than 0.

"=NL(""Sum"",""Cust. Ledger Entry"",""Remaining Amount"",""Customer No."",NF(,""No.""),""Due Date"",""<="&NP("eval",'Current Due'!$D$1)&""")",">0"

But it still shows customers who have a credit balance.

If I change the >0 to =0 it only shows records that have a zero balance.

If I change the >0 to <0 it shows an error.

Any ideas would be appreciated.

 

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  CascadiaDataworks January 13, 2023 21:42

  Happy to look at this, can you provide the full function?

• 0
  
  CascadiaDataworks January 16, 2023 20:50

  As I think about this more, do you have customers where the remaining amount would be less than 0?  You are probably getting the error because there are no customers that meet the criteria of Customers with remaining amounts less than 0.

• 0
  
  Barry Thompson January 19, 2023 11:43

  Sorry for the delay, I have been on leave.

  The filter should only return customers with a positive balance for the documents within the date range (>0) but it is returning ones with a negative balance, in credit, also.

   

• 0
  
  Barry Thompson January 19, 2023 11:44

  =NL("Rows","Customer",,"Company=","XXX","+No.","*","Payment Method Code","DDEOM","=NL(""Sum"",""Cust. Ledger Entry"",""Remaining Amount"",""Customer No."",NF(,""No.""),""Due Date"",""<="&NP("eval",'Current Due'!$C$1)&""")",">0")