Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
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.