topic Re: Trendline in QlikView

Linear Trendline

rothk8675309 — Sat, 16 Nov 2024 20:51:38 GMT

I am trying make projections using the data I have for future years using the LINEST_M and LINEST_B functions. The problem is that they aren't working with the Fractile() function. I read that aggregation functions need to use the TOTAL qualifier, but when I use it it doesn't give me the correct answer. Is there a way around this issue?

Expression is close to y=m*x+b where b is =LINEST_B(Fractile(TOTAL [My Value],0.9),[Year]) and m is LINEST_M(Fractile(TOTAL [My Value],0.9),[Year])

When I check the linear trendline equation on a line chart b should be close to -3.9 and m should be close to 0.002

Re: Trendline

rothk8675309 — Thu, 16 May 2019 15:27:23 GMT

I found a solution. If someone else has this problem you can model your expression after this one. This will give you the same result as the trendline equation in a chart for a 90th percentile trend.

Slope
=(count(DISTINCT [Year])*Sum(DISTINCT TOTAL [Year]*Aggr(Fractile([My Value],0.9),[Year]))-(Sum(DISTINCT TOTAL [Year])*Sum(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))))/(count(DISTINCT [Year])*Sum(DISTINCT TOTAL Pow([Year],2))-Pow(Sum(DISTINCT TOTAL [Year]),2))

Y-Intercept
=Avg(DISTINCT TOTAL Aggr(Fractile([My Value],0.9),[Year]))-vSlope*Avg(DISTINCT TOTAL [Year])