Hello I'm trying to build a report with all our active customers with outstanding invoices.
I need the outstaning invoices with the amounts, I encounter a difficulty to combine a value in a filter field:
I need to combine from the Cust. Ledger Entry table the field "Closed at Date".
The filter value for this field should show only the values which have a date newer then 30-09-2014 or the values without a date (blank).
my filter in dutch looks like this: =NL("Rijen";"Klantenpost";;"Klantnr.";D5;"Documentsoort";"Factuur|creditnota|terugbetaling|betaling";"+Boekingsdatum";"01-01-2010..30-09-2014";"Bedrag (LV)";"<>0";"Afgesloten op";">"30-09-2014”|’’”;"Open";"waar")
Translated to English:
=NL("rows";"cust. Ledger Entry"';;"Customer No.";D5;"Document type";"Invoice|creditmemo|payment";"+posting Date";"01-01-2010..30-09-2014";"Amount (LCY)";"<>0";"Closed at Date";">"30-09-2014”|’’”;"Open";"True")
When running this report, the rows with the "closet at Date" value Blank are not shown…..
Can anybody help me with this?
thanks in advance.
Regards Peter
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment Thanks for all your help,
it's solved now, with the use of the Filter option.
I've created two lines with filters:
Filter 1: =NL("rows";"cust. Ledger Entry"';;"Entry No.";"Closed at Date";">"30-09-2014”)
Filter 2: =NL("rows";"cust. Ledger Entry"';;"Entry No.";"Open";"True"
The row function contains the join of both filters:
=NL("Rows";"cust. Ledger Entry";;"Entry No.";NP("join";C4;C5);"+posting date";"*")
Regards Peer -
Jet Reports Historic Posts Hi Peter,
have you tried "30.10.2014..|@@" and "30.10.2014..|''" ?
regards
jetsetter -
Jet Reports Historic Posts Hi Peer,
As Jetsetter showed, when you think about dates, you can't think in terms of less than or greater than. You will want to create a range as it was shown.
For example, for everything on a certain date or before, it would be "..30/10/2014". (from the beginning of time up to and including October 30th, 2014)
For everything on a certain date or after, it would be "30/10/2014.." (from and including October 30th, 2014 until the end of time)
Please sign in to leave a comment.