Hi experts :)
I'm making a report that will check the posting date and pick the relational ex. and exchange rate for a given currency, from the currency exchange rate table. The problem is that sometimes we use a different source for exchange rates where one rate is used for the whole month. Navision picks the earlier rate available in the table which is correct. However, if I use an NL function to pick a specific currency exchange rate for a specific date, it will not because it will validate the date and if it does not find this date in the table, no rate will be returned.
Is there a Jet function/formula or an Excel formula that will pick the earlier available date from the table?
Thanks in advance! 8-)
Date
Votes
12 comments
-
Jet Reports Historic Posts Anyone? :(
-
Jet Reports Historic Posts Hi Jackies
I'm pretty new to Jet reporting and have taught myself using the online help, so hopefully I'm not putting you wrong with this solution. I had a similar problem with a different table and got around it with the following:
"Starting Date",NP("DateFilter",,dd/mm/yy)
…where dd/mm/yy is the last date in the range.
Hopefully this helps. -
Jet Reports Historic Posts I think the only way to make this work is to create exchange rates for all the dates and copy the monthly rate to the remaining dates… :roll:
-
Jet Reports Historic Posts Hi Jackies
I'm pretty new to Jet reporting and have taught myself using the online help, so hopefully I'm not putting you wrong with this solution. I had a similar problem with a different table and got around it with the following:
"Starting Date",NP("DateFilter",,dd/mm/yy)
…where dd/mm/yy is the last date in the range.
Hopefully this helps.
Thanks RedMeg, for your reply! I checked NP("DateFilter",,dd/mm/yy) but the problem is the date range always start from the first available rate rather than checking the last and going back:
How ti works with NP("DateFilter",,dd/mm/yy) —>ending date
How I want it to work <—ending date -
Jet Reports Historic Posts If you sort Starting Date descending
"-Starting Date",NP("DateFilter",,dd/mm/yy)
this should work. By entering the formula the "wrong" exchange rate is shown but after running the report you get the right one. -
Jet Reports Historic Posts Thanks cat for your input.
Unfortunately, neither this work. :( -
Jet Reports Historic Posts I tried it here with the data from your screenshot and as result I got
43,1707 with =NL(;"Währungswechselkurs";"Bezug auf Wechselkursbetrag";"Währungscode";"TTT";"Startdatum";"..15.04.2010")
and
39,62 with =NL(;"Währungswechselkurs";"Bezug auf Wechselkursbetrag";"Währungscode";"TTT";"-Startdatum";"..15.04.2010")
This should be the correct result.
How does your formula looks like? -
Jet Reports Historic Posts I'm using this formula:
=NL("Lookup","Currency Exchange Rate","Exchange Rate Amount","Currency Code","EUR","-Starting Date",NP("DateFilter",,Options!$D$6),"company=",$E7)
where Options!$D$6 equals to 01/01/2009
I have followed your example and constructed a new value like the one you used above but still get the same result:
=NL("Lookup","Currency Exchange Rate","Exchange Rate Amount","Currency Code","EUR","-Starting Date","..01/01/2009","company=",$E7)
Still no change…always start from the beginning of the currency table instead of from the date filter end. :( -
Jet Reports Historic Posts Why do you use 01.01.2009 - I thought you would like to get the entry before 15.04.2010?
With the exchange rate table from your screenshot you can't get a result bei filtering starting date until 01.01.2009, because the first entry is with 01.01.2010. -
Jet Reports Historic Posts Sorry if I messed it up a bit :oops:
The screenshot and 15/04/10 is for the example. 01/01/2009 is the actual date I'm using in one of my reports, of course with different currency table data but same situation: there are exchange rate gaps. -
Jet Reports Historic Posts What about
=NL("Last",NL("Filter","Currency Exchange Rate","Exchange Rate Amount","Currency Code","EUR","Starting Date",NP("DateFilter",,Options!$D$6),"company=",$E7))?
HTH
rmw -
Jet Reports Historic Posts YES! Got same reply here :D
Please sign in to leave a comment.