Hello everybody!
I'm stick on a formula and I don't know how to solve it. Hope you guys can help me.
I want the following in one formula:
A list of all the Customer No. from the table Cust. Ledger Entry with these filters:
-> Customer No. is an option via a lookup formula. (can be more than 1 Customer No. off course)
-> The sum of the field Remaining Amount from the same table for each customer may not be 0.
This is the formula I already have:
=NL("Rows";"Cust. Ledger Entry";"Customer No.";"Customer No.";Options!$C$2;"Remaining Amt. ($)";NL("Sum";"Cust. Ledger Entry";"Remaining Amt. ($)";"Customer No.";Options!$C$2)<>0)
Problem is that the NL Sum will calculate the Remaining Amount on the total of Customer No.'s on the Options page (e.g. CUS001..CUS010). I want it calculated per Customer No.
Further, the error I get from the formula is Invalid Filter "True". I understand why, but I don't know how to correct the error… :evil:
I'm hoping the specialist of this forum can help me! :)
Date
Votes
7 comments
-
Jet Reports Historic Posts Official comment Hi Hughes!
I will use Hans' formula. It works perfect and it's much more efficient.
Your formula also works perfect!
Your explanation makes everything more clear now. I know understand a bit more of the calculated filters.
Thank you! :) -
Jet Reports Historic Posts Hi Bertynp,
Let me see if I got this right… you want to select all customers which have an outstanding amount? In that case I would use the customer table, as Balance (LCY) is the sum of the remaining amount of the customer ledger entries.=NL("Rows","Customer","No.","No."Options!$C$2,"Balance (LCY)","<>0")
Does that work for you? -
Jet Reports Historic Posts Hi Hans,
Thanks for you quick reply!
Your formula is the perfect solution! Apparently I was searching it to far… Still got a lot to learn! :roll:
I don't want to be difficult but I'm still curious if there is a solution to the formula I have given… ;)
You never know if you have to make such a report.
But thanks again Hans! :D -
Jet Reports Historic Posts Hi,
So I'll start out by saying that you should definitely use Hans' formula since that is a much faster and more elegant solution to the problem.
As far as your existing formula, it can be fixed, but there are several problems with it. First, for performance reasons, you should never replicate unique values out of a large table like the Cust. Ledger Entry when you can get those values from a smaller table. So we'll start by getting the customer numbers from the Customer table and use Link= to only get the numbers that have ledger entries like this:=NL("Rows";"Customer";"No.";"No.";Options!$C$2;"Link=";"Cust. Ledger Entry";"Customer No.";"=No.")
Now, as far as getting only the customer numbers with a non-zero sum from the Customer Ledger Entry, we can do that with a calculated filter. Calculated filters will be evaluated for each record in the rows replicator, which is what you had problems with in your previous sum formula.=NL("Rows";"Customer";"No.";"No.";Options!$C$2;"=NL(""Sum"";""Cust. Ledger Entry"";""Remaining Amt. ($)"";""Customer No."";NF(;""No.""))";"<>0";"Link=";"Cust. Ledger Entry";"Customer No.";"=No.")
Now, actually given that we are filtering for a non-zero sum from the Cust. Ledger Entry, we can actually remove the Link= since the customers who will have a non-zero sum MUST have records in Cust. Ledger Entry so the Link= is now unnecessary. So the final formula might look like this:=NL("Rows";"Customer";"No.";"No.";Options!$C$2;"=NL(""Sum"";""Cust. Ledger Entry"";""Remaining Amt. ($)"";""Customer No."";NF(;""No.""))";"<>0")
Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hilton,
What are you using the NF function for? I get confused at that part. I am trying to filter my data by looking at the sum of my vendors' payments (only show vendors with total payments of $600 or over for the year), and I'm linking the vendor table to the vendor ledger entry by using the vendor no. but I cant get it to work right.
=nl("Rows","vendor",,"1099 Code",$C$7,"Company=",$C$2,"Date Filter",$C$3,"link=","Vendor Ledger Entry","Vendor No.","=No.","=nl(""sum"",""vendor ledger entry"",""amount"",""Company="",$C$2,""Document Date"",$C$3,""Document Type"",""payment"",""vendor no."",$G16,""amount"","">599"")","*")
V/R,
Gustavo -
Jet Reports Historic Posts Hi Gustavo,
So you've got a couple things wrong in this formula. First, you're defining the calculted formula AFTER the link= which means it would be getting applied to the Vendor Ledger Entry instead of the Vendor which is not what you want. Also, as I pointed out in my previous solution, the Link= is not even necessary in his case and it's not necessary in yours either.
The next problem you'll have is your cell references within the calculated formula. It's best to actually unquote the formula and reference those directly. You'll see from the example below how to do this.
As far as the nested NF in the calculated formula, you use that to link it to the outer formula. For each Vendor record, Jet will evaluated your calculated sum formula. However, without the NF in the calculated formula, the sum would just sum up ALL the Vendor Ledger Entries instead of only summing up the entries for the specific Vendor that it's checking.
You have a filter on the Date Filter field but the Date Filter is a flow filter field which only applies to flow fields, which you're not using, so this filter shouldn't be necessary.
Since you want to filter for sums greater than $600, you have to make that the filter portion rather than embedding it in the calculated field. Also, in order for Jet to know that the result of the calculation is a number and do the filter correctly, you'll need a special NUMBER& prefix on the front of the filter. The finished version might look something like this:=nl("Rows","vendor",,"1099 Code",$C$7,"Company=",$C$2,"=nl(""sum"",""vendor ledger entry"",""amount"",""Company="","""&$C$2&""",""Document Date"","""&$C$3&""",""Document Type"",""payment"",""vendor no."",NF(,""No.""))","NUMBER&>=600")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thank you Hughes!
Once again I am saved by your help. That worked great. I will have to go over what you explained later in more detail to further understand WHY it worked, but with your written explanation and a working example to go by, I should be ok.
Thanks again!
V/R,
Gustavo
Please sign in to leave a comment.