Any ideas why this date filter wouldnt work?=-(NL("Sum";"G/L Entry";"Amount";"G/L Account No.";H16;"Posting Date";$F$10;"company=";$F$5))The date filter in cell F10 is 27/03/2010..27/03/2012 , compiled using =NP("DateFilter";D11;D10))De G/L Account No. in cell H16 is 1..199999, this field is extracted from the G/L account table, and is a account range taken from a field in the "totaling" window.
Im pretty much reproducing the Chart Of Accounts, just taking out some 0's here and there. and trying to apply a date filter.
Im using this line to bring up a list of a totaling G/L account numbers =NL(C14;"G/L Account";;"Account Type";"Total";"Company=";$F$5)C14 is just a numbering series, similar thing to using "rows" function far as i know
This query doesnt return an error value but the amount it returns is barely enough for a months. I dont understand how this is possible. The Weird thing is , it doesnt matter which dates i use, if i apply a date filter i get the same amount each time, if i dont apply a date filter i get another amount they are always the same, sometimes jetreports makes me so angry :cry:
Any help would be most appreciated as per Usual,
Kind Regards,
Mattias Lemmens
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment If this is just a case of not knowing why you're getting a particular number, you could just turn your NL(Sum) into an NL(Rows) and check the individual lines. If your fiscal year ends June 30 then going from June 1 to March 3 would cross from one fiscal year to another. So I suspect you are actually getting closing date entries in your range (which would be designed to reverse the transactions of the previous year). In NAV as you probably know, closing date entries all occur on the last date of the fiscal year and their dates all start with a letter. In English it's C so they would be C30/06/2011. In other languages the letter is different (possibly it's U for you?). Anyway, assuming you know the correct letter, you can filter out closing dates in Jet by changing your filter to something like this:
<>U30/06/2011&01/06/2011..01/03/2012
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Matthias,
Hmm, I'm not exactly sure what is going on here for you. My first thought is that you are not actually running your report by selecting Jet -> Report or Jet -> Refresh. Design mode values are optimized for speed and may not be correct in some circumstances, so you should always run the report to be sure you have the correct data.
My other thought is that you have a problem with your cell referencing. I can't confirm this since I can't see your actual report with the cell references. Could you have copied some rows with absolute cell references and now all those cell references are referring to the top row instead of referring to the row they are on?
Hope one of these helps. Otherwise, maybe you could send your report (in design mode or else run against a test database so your data will not be displayed).
Regards,
Hughes -
Jet Reports Historic Posts Hey Again, ive done a bit more extensive testing, trying the same formula with just one G/L account (700000, sales fabrics, this value should always be positive and should increase gradually as you add more months). Created a new date filter starting with
still using this code.=-(NL("Sum";"G/L Entry";"Amount";"G/L Account No.";H14;"Posting Date";F11;"company=";$F$5))
Altering dates after each step.
01/02/2012..01/03/2012 = 336.867 (this represent sales of textiles for the previous month)
01/01/2012..01/03/2012 = 550.736 (so far so good)
01/12/2011..01/03/2012 = 975.482 (still looking good)
01/11/2011..01/03/2012 = 1.308.902 (good)
fast forward a bit
01/09/2011..01/03/2012 = 2.063.905
01/07/2011..01/03/2012 = 2.461.371 (still fine, summer months are always slow)
01/06/2011..01/03/2012 = -1.309.767 (!? wt? , our fiscal year ends on the 30th of June, even though G/L entry should contain all lines from all years, i cant help but feel that it has to have something to do with this. Weve had Navision for over 3 years now, and the G/L numbers have never been altered or anything.
Any ideas? -
Jet Reports Historic Posts Thank you, that is what my colleague told me eventually when i managed to reach him :P
Please sign in to leave a comment.