Is it possible to have an exception for a date filter? I have created an inventory turns jet report to determine our slow moving inventory. To calculate the turns on an item we have inserted an excel formula that calculates the average inventory based on the activity of that item for a specified start & end date. The problem that I am experiencing is that the report is showing an item that has been recently received, but has no activity, to be slow moving. I do not want receipt of an item included if that receipt took place within 2 months prior to the specified end date. Is there a way to do this? Maybe on a nested filter based on document type?
Example: Report is ran with a start date of 1/1/2015 and end date of 9/30/2015. I do not want receipts of items included if they posted less than/equal to 2 months prior to 9/30/2015.
I hope this makes sense…
Thank you,
Nicole
Date
Votes
6 comments
-
Jet Reports Historic Posts If I understand your post correctly, you want the user to enter a start and end date but you want to modify the end date for the actual report? Meaning, they enter in an end date of 10/31/15 but you want the end date for report to actually be 08/31/15?
If that is the case, you can have them enter in their end date and use Excel's EOMONTH function to modify the end date to always be 2 months prior to that date.
=EOMONTH(<<End Date Cell Reference>>, -2) -
Jet Reports Historic Posts Yes, that is how I want the date filter to be. However, is there a way to only apply that filter for Purchase & Transfer receipts only? I would still want to include any sales shipments that took place…
-
Jet Reports Historic Posts Without seeing the report and how you are building it, my guess is that may need to do a grouping report for the two "groups" of document types for each item to get the sum of those transactions or you just have a Sum calculation with one date set and another Sum calculation with the other date set.
-
Jet Reports Historic Posts Ok, I think I get what you are saying. Attached is the report I am working with…in case that will help any further.
-
Jet Reports Historic Posts You could put in cell I10
=NL("Sum","Item ledger entry","Quantity","Entry Type","<>Purchase&<>Transfer","Item No.","@@"&$F10,"Location Code",$D$5,"Posting Date",NP("DateFilter",I$3,I$4))+NL("Sum","Item ledger entry","Quantity","Entry Type","Purchase|Transfer","Item No.","@@"&$F10,"Location Code",$D$5,"Posting Date",NP("DateFilter",I$3,EOMONTH(I$4,-2)))
So, you sum up the non-Purchase and non-Transfer based on full date and then add to that the sum of the purchase and transfer with the altered months. -
Jet Reports Historic Posts Thanks so much for this, it worked!! :)
Please sign in to leave a comment.