Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
rothk8675309
New Contributor II

Linear Trendline

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

1 Solution

Accepted Solutions
rothk8675309
New Contributor II

Re: Trendline

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])

1 Reply
rothk8675309
New Contributor II

Re: Trendline

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])