Hi,
I need to return a price which sits in a modified NAV table that is filtered by Location, Item and date.
There is a Starting Date field and Ending date field that the price is current for. Sometimes it's the same date (i.e. valid for one day) but over weekends, it could be a number of days.
What I'm having trouble with is the filter to extract the price if the actual date falls between a different Start and End date
=NL("first","Price rate","rate","datasource=",$C$5,"Company=",$C$6,"Location Code",$G10,"Product No.",$H10,"Component code","ABC","Starting Date",???,"Ending Date",?????)
I was expecting just to use <,>,= in front of the Date I want to use but they seem to be invalid options.
The end of the formula is where I'm stuck. Although, I may well be way off course!
I hope this makes sense….. :?
I had a search for similar questions but couldn't find any, so any help would be gratefully accepted! :D
Many thanks,
Stuart
Date
Votes
4 comments
-
Jet Reports Historic Posts Instead of < or >, use ..
So for dates before (and including) a date use ..31/01/2016 and for dates after use 31/01/2016..
You can also use the date filter to build it eg NP("DateFilter",StartDate, EndDate) where is you don't have a start date or an end date you just leave the blank.
So for yours, I'd do something like:
=NL("first","Price rate","rate","datasource=",$C$5,"Company=",$C$6,"Location Code",$G10,"Product No.",$H10,"Component code","ABC","Starting Date",NP("DateFilter",YourDate,),"Ending Date",NP("DateFilter",,YourDate)) -
Jet Reports Historic Posts Thanks Teresa.
Unfortunately, that doesn't work and is something I've previously tried. It just returns a blank cell.
It pulls out the rates if the rates Starting and Ending Dates are the same as the date I want the rated extracted for, but not if the date is want is between the Starting and Ending Date field in the table, e.g. I want to extract the rate for 01/05/16 but the rates table Starting Date field is 30/04/16 and Ending Date field is 03/05/16.
TIA -
Jet Reports Historic Posts I had my filters the wrong way round. It should be:
=NL("first","Price rate","rate","datasource=",$C$5,"Company=",$C$6,"Location Code",$G10,"Product No.",$H10,"Component code","ABC","Starting Date",NP("DateFilter",,YourDate),"Ending Date",NP("DateFilter",YourDate,))
Which for the example you gave would give:
=NL("first","Price rate","rate","datasource=",$C$5,"Company=",$C$6,"Location Code",$G10,"Product No.",$H10,"Component code","ABC","Starting Date","..01/05/2016","Ending Date","01/05/2016..")
I really can't think of a reason why that wouldn't work. -
Jet Reports Historic Posts that looks great! Thanks Teresa :D
Please sign in to leave a comment.