topic Re: Add the new rows with the value of rolling average value in QlikView

Add the new rows with the value of rolling average value

yonghanfi — Wed, 10 Jan 2018 13:50:56 GMT

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:

LOAD

date,

Currency,

rate ,

FROM sourceData.qvd

where Currency_id = 'DKK'

;

join

LOAD

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

'DKK' as currency

AUTOGENERATE 1

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

currency:

NOCONCATENATE

load

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 13:54:04 GMT

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

yonghanfi — Wed, 10 Jan 2018 14:21:34 GMT

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 14:31:54 GMT

Try this?

currency:

NOCONCATENATE

load

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;

Or else please share QVD/QVW??

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

yonghanfi — Wed, 10 Jan 2018 14:54:58 GMT

Sorry, still not working. After reload, it complain "Invalid expression". My testing results show that whenever I use sum or other aggr function into to the script, it start to complain "Invalid expression".

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 14:58:41 GMT

Fine, Can you provide sample application