topic "TENDENZA" (TREND) function in excel in QlikView

"TENDENZA" (TREND) function in excel

Tue, 26 Apr 2011 20:31:40 GMT

Hi All!

My customer has this actual values for the first 14 days of the month, considering the month has 23 working days. He needs to calculate the values for the rest of the days (from 15 th to 23 th).

first 14 days:

-507 -346 -389 -391 144 -485 -392 -252 -162 742 5 -116 11 -430 (from 1th to 14 th)

To calculate the values for the rest of the month (forecast trend) he has to apply the TREND FUNCTION of excel and the funtion returns this values:

-179,291 -191,243 -203,196 -215,149 -227,101 -239,054 -251,007 -262,96 -274,912 (from 15 th to 23 th)

I tried to replicate this application in a line chart on qlikview using "linest_m and Linest_B function" but the result is incorreect.

Could you help me please?

thanks a lot

Alessandro

"TENDENZA" (TREND) function in excel

vgutkovsky — Tue, 26 Apr 2011 23:04:40 GMT

Alessandro,

Really interesting problem! I don't think QV has a TREND function built in. But I did some research and Excel's TREND function projects a straight line using the method of least squares. In fact, it essentially acts as a mask for the LINEST function. The QV linest_m function is too confusing for me to decipher, but this is something we can implement manually in QV. The expression that LINEST produces is the following:

m = sum((x-avg(x)*(y-avg(y))/sqr(sum(x-avg(x)))

However, I see from your example that you are setting 'b' to be 0. This makes it even easier:

m=sum(x*y)/sum(sqr(x))

You just use the value of 'm' to multiply by the day and get your updated value. I've replicated that logic in QlikView. See attached example. Cool problem, I haven't had to do math in a while 🙂

Regards,

"TENDENZA" (TREND) function in excel

Wed, 27 Apr 2011 07:44:13 GMT

Thanks for your support Vlad!

Alessandro

"TENDENZA" (TREND) function in excel

vgutkovsky — Wed, 27 Apr 2011 12:45:33 GMT

No problem, please verify correct answers 🙂