topic Replace missing values with avg of previous years in QlikView

Replace missing values with avg of previous years

alwinsch — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Replace missing values with avg of previous years

antoniotiman — Fri, 02 Jun 2017 10:35:40 GMT

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

Re: Replace missing values with avg of previous years

alwinsch — Fri, 02 Jun 2017 10:44:15 GMT

Hi Antonio,

i can't do this in load script

I calculate Vincome as variable after load.

regards,