Following recommendation on how to great a manual trendline from John Witherspoon, I'm able to create a manual trendline in my chart and in a text box for simple data.
However, I really need a trendline for accumlated data -- but the slope (m) calculated by Qlikview chart (Trendline in Expressions tab) when I accumlate my data is not the same as when I calculate manually. Visual inspection shows that the Qlikview chart trendline is accurate, while my manaul calculation is not.
Here's my Expression for calculating accumulation (note that I've simplified this, as I know I could select "accumulate" in the Expressions tab, but I actually need the Ln/Log of this, along with some other info, so I need to manually accumulate -- but even the simplified version is causing problems)
Works/Accurate in Line Chart with Qlikview adding "Trendline: Linear":
=RangeSum(Above(Sum([Disconnects]),0,[Disconnect Month]))
The above does a great job of accumulating my customer disconnects, and works well on a line chart where the only Dimension is [Disconnect Month].
Selecting "Trendlines: Linear" for the above expression in the Expression tab also seems to work very well.
However, adding another expression that manually creates the trendline on the same chart is not identical to the Qlikview/chart Trendline. My guess is that the "Above" function isn't really intended for use like this (I assume it's having problems putting [Disconnect Month] in the "correct" order as there's no context of what order it should be in for this expression):
Manual Creation of Trendline in Same Line Chart Doesn't Work (isn't accurate):
=
LINEST_M(TOTAL AGGR(RangeSum(Above(Sum([Disconnects]),0,[Disconnect Month]))
,[Disconnect Month])
,[Disconnect Month])*[Disconnect Month]
+
LINEST_B(TOTAL AGGR(RangeSum(Above(Sum([Disconnects]),0,[Disconnect Month]))
,[Disconnect Month])
,[Disconnect Month])
Alternative solution: What I really need is the slope (m) of this trendline either in a text box or in a Straight Table -- I am able to create a straight table with the accumulation to match the line chart (as I can set the Sort Order by [Disconnect Month]), but I'm not sure how I would use this result, "Column(1)", with LINEST_M in the straight table to show the slope (when I use the LINEST_M expression above in the my straight table, Sort Order by [Disconnect Month], I still get the incorrect result).
Any thoughts here? Am I out of bounds trying to use "Above", "RangeSum" and "LINEST_M" all in the same expression to calculate the slope of an accumulated data set trendline?
