I am trying to create an item list by Location with sales figures for the past 6 months and the past 12 months. How would I set up a filter to create a range from date of report run back 6 or 12 months? And what would be the best way to use them? I would be using the Item Ledger Entry table…I'm assuming that I need to use the sum feature in the NL function…but not sure how to do the date range. Here's the NL function so far…
=NL("Sum","Item Ledger Entry","Quantity","Location Code",Options!$D$5,"Entry Type","Sale","Item No.","@@"&$D6)
Thanks!!
Date
Votes
11 comments
-
Jet Reports Historic Posts Hi SDConant,
When it comes to date calculation and ranges I recommend to read:
http://community.jetreports.com/viewtopic.php?f=7&t=209
http://community.jetreports.com/viewtopic.php?f=7&t=210
http://community.jetreports.com/viewtopic.php?f=7&t=217
In the Item table you may want to look at Sales (Qty) field. This field sums the invoiced quantity field from the value entry table, and allows a flowfilter on date and location.
Good luck with the examples and your report! Have a very nice day! -
Jet Reports Historic Posts to get your result, you need to play a little with these 3 functions:
=now()
=date()
=eomonth() (end of month)
I've done this once with these three.
Good luck! -
Jet Reports Historic Posts actually your nl would look like this
prev 6mo's
NL("Sum","Item Ledger Entry","Quantity","Location Code",Options!$D$5,"Entry Type","Sale","Item No.","@@"&$D6,"Posting Date",$b$2)
and
prev 12 mo's
NL("Sum","Item Ledger Entry","Quantity","Location Code",Options!$D$5,"Entry Type","Sale","Item No.","@@"&$D6,"Posting Date",$b$3)
I am going to assume that your entering your date from the options box and it is in Options!$D$4 …
B2's formula would be nl("Datefilter",DATE(YEAR(Options!$D$4),MONTH(Options!$D$4)-6,1),options!$d$4)
B3's formula would be nl("Datefilter",DATE(YEAR(Options!$D$4)-1,MONTH(Options!$D$4),1),options!$d$4)
So .. if you entered 06/30/08 in options!$d$4, b2 would be 12/01/07..06/30/08 and b3 would be 06/01/07..06/30/08
hope this helps. -
Jet Reports Historic Posts actually your nl would look like this
prev 6mo's
NL("Sum","Item Ledger Entry","Quantity","Location Code",Options!$D$5,"Entry Type","Sale","Item No.","@@"&$D6,"Posting Date",$b$2)
and
prev 12 mo's
NL("Sum","Item Ledger Entry","Quantity","Location Code",Options!$D$5,"Entry Type","Sale","Item No.","@@"&$D6,"Posting Date",$b$3)
I am going to assume that your entering your date from the options box and it is in Options!$D$4 …
B2's formula would be nl("Datefilter",DATE(YEAR(Options!$D$4),MONTH(Options!$D$4)-6,1),options!$d$4)
B3's formula would be nl("Datefilter",DATE(YEAR(Options!$D$4)-1,MONTH(Options!$D$4),1),options!$d$4)
So .. if you entered 06/30/08 in options!$d$4, b2 would be 12/01/07..06/30/08 and b3 would be 06/01/07..06/30/08
hope this helps.
that would be NP I think… -
Jet Reports Historic Posts very wierd … i have always used nl and it works fine … but the function wizard does show it under the NP functions.
thanks for the correction Jan -
Jet Reports Historic Posts very wierd … i have always used nl and it works fine … but the function wizard does show it under the NP functions.
thanks for the correction Jan
np (pun intented ;)) -
Jet Reports Historic Posts Something else you can use (very good when scheduling the report) is the now() property of excel.
So if you want the data from a year back starting from the moment it is run your filter formula would look like this:
NP("Datefilter";DATE(YEAR(Now())-1,MONTH(Now()),Day(Now())))
edit: today, this wil return this date filter: "2007-06-27.." -
Jet Reports Historic Posts a quick FYI.
NP("DateFilter") and NL("DateFilter") are both correct. In older versions of Jet Reports, we only had NL & NF functions. As we expanded the functionality of Jet Reports, we decided to move certain functions to the NP. Any function that did not have to read the database was moved to NP which includes DateFilter. This leaves the NL for any function that needs to read the database. However, for backward compatibility with older versions, we left any existing NP function in NL also.
The current preferred method is to use NP("DateFilter") and this is how you will see this documented.
Additionally, care should be taken when using the Excel =Now() or =Today() functions. Because these are what are known as volatile functions and can cause your reports to run slower as Excel keeps trying to recalculate them as Jet Reports runs. Any time you use these functions in a Jet Report, you should put them in an NP("Eval") function. For example
=NP("Eval","=NP(""Datefilter"",DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())))")
This then prevents the value from being calculated many times, it only gets calculated once, when the report first starts.
Ok, so this isn't so quick. -
Jet Reports Historic Posts =NP("Eval","=NP(""Datefilter"",DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())))")
This then prevents the value from being calculated many times, it only gets calculated once, when the report first starts.
Ok, so this isn't so quick.
Nice!
But I've never experienced the "recalculation", when does it occurs?
I must say I actually don't work with three "now()"'s, i use one and link to it:
=NP(""Datefilter"",DATE(YEAR(C2)-1,MONTH(C2),DAY(C2)))
where C2 is a cell containing "=now()"
Thanks for some bg info! As for the backward compatibility, I thought it would be something like that… -
Jet Reports Historic Posts Recalculation can be triggered by most anything, and Excel decides when to recalculate. Hiding cells and inserting rows or columns seems to trigger recalc of entire worksheets. However, a recalc of a single cell can occur when the cell contains a volatile functions like =NOW(). Every cell that references that cell will cause the =NOW() to be recalc'd. In most cases this is not a problem, however, if you have thousands to cells referencing the =NOW(), it can slow down report performance. By putting the =NOW() in a NP("Eval") we avoid the recalculation.
-
Jet Reports Historic Posts Thanks Sherman….that's exactly what I was looking for. Thanks everyone else who put thought into this as well!!
Now where would I put the NP/eval? Right in the NL statement? or reference it from another cell??
Please sign in to leave a comment.