Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! 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
Highlighted
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.