Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
alwinsch
Contributor

Replace missing values with avg of previous years

Hi all,

I have a table (see attached) with missing values for the months to come in 2017.
i want to fill this cells with the avg growth over last 3 years.

it is some kind of forecasting.

thanks for your help.

A.

2 Replies
antoniotiman
Honored Contributor III

Re: Replace missing values with avg of previous years

Hi Alwin,

try

Temp:
LOAD Quarter,
Month,
[2013],
[2014],
[2015],
[2016],
If(Len(Trim([2017]))=0 or [2017] = '-',Round(RangeAvg([2016],[2015],[2014]),0.01),[2017]) as [2017]
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1280318-281476/Example%20Data.xlsx"
(ooxml, embedded labels, table is Blad1);
Table:
CrossTable(Year, Value, 2)
LOAD Quarter,
Month,
[2013],
[2014],
[2015],
[2016],
[2017]
Resident
Temp;
Drop Table Temp;

Regards,

Antonio

alwinsch
Contributor

Re: Replace missing values with avg of previous years

Hi Antonio,

i can't do this in load script

I calculate Vincome as variable after load.

regards,

A.