Backdate Customers with Credit Balance

Questions and Answers asked by users to users, about how others use Jet Reports.

For additional "how to" information, please visit our online Knowledge Base https://jetsupport.jetreports.com/hc/en ... ofessional
Forum rules
The Jet Reports Community forums are not a guaranteed support medium. Official support for Jet Reports is available only through the support desk at http://support.jetreports.com. Staff responses to posts on the forum are not guaranteed.
Oracle
Posts: 51
Joined: Thu Jan 24, 2013 4:18 am

Backdate Customers with Credit Balance

Postby Oracle » Mon Jun 05, 2017 3:05 am

Good Day all,

I am using NAV 2016.
I am trying to retrieve just a single total balance for all customers with a credit balance.
The issue comes in that i need to backdate the report from time to time to see what the "Total Credit Balance" was say month end 3 months ago so i cant just get all customers with a credit balance from the customer table - i have to use the Detailed customer ledger entries and apply filters somehow.

The following formula seems to work with normal aging to back date the report but i am not sure how to apply the back dating logic to retrieve the total credit balances at that time.

Any help is greatly appreciated.

=NL("Sum","Detailed Cust. Ledg. Entry","Amount (LCY)","Posting Date",$C$5,"Link=","Cust. Ledger Entry","Entry No.","=Cust. Ledger Entry No.","Posting Date",C$5)

Kind Regards

Tom

Heather
Posts: 122
Joined: Tue Jul 09, 2013 2:04 pm

Re: Backdate Customers with Credit Balance

Postby Heather » Mon Jun 05, 2017 12:53 pm

Can you change the Posting Date you reference in C5? change that reference to another cell, and put the oldest date you want to retrieve there?

Oracle
Posts: 51
Joined: Thu Jan 24, 2013 4:18 am

Re: Backdate Customers with Credit Balance

Postby Oracle » Tue Jun 06, 2017 4:44 am

Hi Heather,

Thanks for your response.
Yes the dates can be changed but i think i am not explaining myself well.

Please see the attached Document.

What i am trying to do is run a report for say 31/03/17 but we are now in say June 2017 so effectively backdating.
I need to get the SUM of all the Credit balances for Customers who had Credit Balances only as at the end of march and return a single total value for that month.
The formula must exclude Customers with Debit balances.

Rows 9 to 11 (in light yellow) are the individual accounts.
I am after ROW 12 which shows just the Totals of Credit Balances.
Notice L12 is 0 because L11 is a positive balance.

I can probably list each customer and get their balance and then apply the logic i have used in the spreadsheet but there are over 250 000 customers so the spreadsheet is going to grow and speed issues

Hope this helps explain my issue better.

Many thanks

Thomas
Attachments
Test Credit Balances.xlsx
(12.33 KiB) Downloaded 11 times

Heather
Posts: 122
Joined: Tue Jul 09, 2013 2:04 pm

Re: Backdate Customers with Credit Balance

Postby Heather » Tue Jun 06, 2017 6:54 am

I think there must be a significant difference in our databases, because I don't see the need to link "Detailed Cust. Ledg. Entry" and "Cust. Ledger Entry" the way you have done.
Since I don't often deal with these tables, I'm going to suggest that someone else might be better able to help - rather than my potentially confusing the situation with a solution that only works in my system.

Sorry Oracle

Oracle
Posts: 51
Joined: Thu Jan 24, 2013 4:18 am

Re: Backdate Customers with Credit Balance

Postby Oracle » Tue Jun 06, 2017 8:03 am

Hi Heather,

No problem - the only reason i am using the detailed ledger entries is because i have to include/exclude entries and applications depending on when they happened as if i ran the report back in time so i have a snapshot of what the entries were incase old entries have since been applied by newer entries.

Anyway, thanks for your time - hopefully someone can still assist.

Many thanks

Tom

Gasper
Posts: 3
Joined: Wed Jun 07, 2017 8:39 am

Re: Backdate Customers with Credit Balance

Postby Gasper » Wed Jun 07, 2017 9:41 am

Hey Tom, where do you have the criteria which tells Jet to only pick customers which have a credit balance at the specific date?

Gasper
Posts: 3
Joined: Wed Jun 07, 2017 8:39 am

Re: Backdate Customers with Credit Balance

Postby Gasper » Wed Jun 07, 2017 9:48 am

Hey Tom, where is the condition in your sum formula which tells Jet to only sum Customers which have a credit balance at the given date?

Oracle
Posts: 51
Joined: Thu Jan 24, 2013 4:18 am

Re: Backdate Customers with Credit Balance

Postby Oracle » Thu Jun 08, 2017 12:28 am

Hi Gasper,

Thats my problem - i have to sum per customer and then apply a filter to the negative results per customer and then sum only those again so essentially a sum in a sum in a single formula

Usually in SQL i would do a select and group by customer and then do a select on that result set where the balance <0 and then just get the final total which is all the credit balances.

I am struggling to do this logic in Jet :cry:

Gasper
Posts: 3
Joined: Wed Jun 07, 2017 8:39 am

Re: Backdate Customers with Credit Balance

Postby Gasper » Mon Jun 12, 2017 9:42 am

Can you use the Customer table instead? .. Here you might be able to get the Balance w/o too much hassle if this table has a date filter you could use:

=NL("Rows","Customer","No.","Balance Due",C3,"Date Filter",C2) <== this would give you the Customer# if this customer had a <0 'Balance Due' (C3 is <0) at the given date.

and then in the next cell you add another Jet function (like the one you were using) to show the balance of the Customer found from the function above.


Return to “How do I?”

Who is online

Users browsing this forum: Bing [Bot] and 12 guests