Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
alwinsch
Creator
Creator

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
Master III
Master III

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
Creator
Creator
Author

Hi Antonio,

i can't do this in load script

I calculate Vincome as variable after load.

regards,

A.