Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have two tables as below, one is Rate and the other is Sales:
What I want is to calculate the Amt according to the VL_Rate, but the Rate is defined by the Active_Date and Expiry_Date,
in another word, the sales Date must between them.
For example, if I want to see the AUD Market Amount of USD, I should calculate as below:
Market Date Amount
AUD 2012-10-19 59802/1.03
AUD 2013-08-12 62321/1.04
AUD 2013-11-12 60874/1.04
But I do not know how to write the expression for this, anyone can help me?
Thank you very much.
Finally, it should be like this:
Hi
PFA Hope this will helps you
Hi Nirmal,
Thank you for you reply, but it seems not correct,
The first record:
Amt_According to Date of AUD 2012-10-19 should be:59802/1.03=5860.19, not 28889.85
Hi
PFA now
Hi Nirmal,
Thank you much, but sorry for my mistake, I want to clarify what I want to get, first we must select the field To_Rate,for example we select USD,that means we want to see the amt of US dollar. In this case, if USD is selected, the exchange rate should be 1.03 for date 2012-10-19 and 1.04 for date 2013-08-12, then the amt for the date 2012-10-19 should be amt/1.03 and for date 2013-08-12 should be amt/1.04.
The result should be:
Market Date Amt Amt_According to Date
AUD 2012-10-19 59802 59802/1.03
2013-08-12 62321 62321/1.04
2013-11-12 60874 60874/1.04