0

First Date are not included in filtered data range for nl(sum)

Pahala Juniver Nicolas

November 22, 2024 09:41

None

Follow

Hi,

I want to use nl(sum) to sum the amount in G/L Entry with filtered range date in year 2023 based from function below:
=NL("SUM";"G/L Entry";"Amount (LCY)";"Posting Date";T$5;"Order Dimension";$C11;"G/L Account No.";$D$2)

1. $C11 formula = NL("Rows";"G/L Entry";"Order Dimension";"G/L Account No.";$D$2;"IncludeDuplicates=";"False";"Order Dimension";"<>'@@")
2. $T$2 Value = "01/01/2023..31/12/2023"

The Jet Report run successfuly, however the entries with posting date 01/01/2023 are excluded so that the sum result is not balanced with my G/L Entry. I there any errors with the function?

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  CascadiaDataworks November 27, 2024 15:55

  That is interesting.  Is it possible those that are missing are closing entries on 01/01/23? 

  Here is how I might go about diagnosing.  Create an NL(Table) with the singular date filter 01/01/23 with your same filters (picking one GL Account where you know there are records on that day).  Do these records show up in the results? If no, perhaps try a filter of 31/21/22..01/01/23 and look at the results.  Do they show up there? You can even try that date filter in your report to see if they show up but using NL Table isolates data for you a little easier.

  On a side note, I would remove any IncludeDuplicates= from your Rows functions.  That filter only applies to NL(Table) but could possibly cause problems in an NL(Rows).

• 0
  
  CascadiaDataworks December 20, 2024 18:49

  Pahala, did you ever get this figured out?  Happy to help look at this if you want.  You can reach me at my username at gmail