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).
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 🙂
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 🙂
