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
Date
Votes
8 comments
-
Jet Reports Historic Posts 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?
-
Jet Reports Historic Posts 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 -
Jet Reports Historic Posts 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 -
Jet Reports Historic Posts 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 -
Jet Reports Historic Posts Hey Tom, where do you have the criteria which tells Jet to only pick customers which have a credit balance at the specific date?
-
Jet Reports Historic Posts 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?
-
Jet Reports Historic Posts 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: -
Jet Reports Historic Posts 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.
Please sign in to leave a comment.