5 Replies Latest reply: Jan 10, 2018 9:58 AM by Anil Samineni

Add the new rows with the value of rolling average value

Let's say I have a Currency table which can be simplified like below:

 date rate currency …. …… ….. 28.12.2017 7,4455 DKK 29.12.2017 7,4449 DKK 30.12.2017 7,4449 DKK 31.12.2017 7,4449 DKK 1.1.2018 7,4449 DKK 2.1.2018 7,4437 DKK 3.1.2018 7,4442 DKK 4.1.2018 7,4449 DKK 5.1.2018 7,4459 DKK 6.1.2018 7,4459 DKK 7.1.2018 7,4459 DKK 8.1.2018 7,4467 DKK

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,

table_temp:

date,

Currency,

rate ,

FROM sourceData.qvd

where Currency_id = 'DKK'

;

join

date(Today()-2 + iterNo())  as date,

'DKK' as currency

AUTOGENERATE 1

While date(Today()-2 + iterNo()) <= AddYears(Today()-2,1);

currency:

NOCONCATENATE

date,

currency,

if(IsNull(rate), RangeAvg(rate), rate) as rate,

currency_key

Resident table_temp

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!

• Re: Add the new rows with the value of rolling average value

RangeAvg() should covered 2 arguments. Can you try this?

If(IsNull(rate), RangeAvg(rate, 0), rate) as rate,

• Re: Add the new rows with the value of rolling average value

I tried RangeAvg(rate, 0), did not work, instead it only assign 0 as rate value after date 9.1.2018. Could you also explain what is the second argument in rangeavg()?

Thanks! Yong

• Re: Add the new rows with the value of rolling average value

Try this?

currency:

NOCONCATENATE

date,

currency,

if(IsNull(rate), RangeAvg(Sum(rate),0), rate) as rate,

currency_key

Resident table_temp Group By date, currency, currency_key

order by exchange_rate_date;