This content has been marked as final. Show 5 replies
Let's say I have a Currency table which can be simplified like below:
Let's assume the date start from 01.01.2016, I need to extend the data to one year in the future e.g. until 8.1.2019, and set the rate be the rolling average of the history data in last 365 days.
what I did was as below,
where Currency_id = 'DKK'
date(Today()-2 + iterNo()) as date,
'DKK' as currency
While date(Today()-2 + iterNo()) <= AddYears(Today()-2,1);
if(IsNull(rate), RangeAvg(rate), rate) as rate,
order by exchange_rate_date;
drop Table table_temp;
but I don't get, why rangeAvg(rate) did not work, instead it only returned the rate value from previous row.
Could anyone give some help? How can I get rate value from the last year's rolling average? Thanks!