Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV-Community
I have the following problem.
I try to rebuild the inbuild Qlikview Trendline.I want to show the slope and the intercept in a textbox.
Therfor i use the following two expressions (for slope and intercept):
Textbox1 (slope):
Linest_m(Total Aggr(aggr((sum({$<MonthDiff={'>=$(vZeitraum12Monate)<=$(vAktuellerMonat)'},
Kat={'Leere'}>}Daten_D)/sum({$<MonthDiff={'>=$(vZeitraum12Monate)<=$(vAktuellerMonat)'},
Kat={'Gesamtzahl Vorgang & Recherche'}>}Daten_D)),MonthNumber),MonthNumber), MonthNumber)
Textbox2 (Y-Axis intercept):
Linest_b(Total Aggr(aggr((sum({$<MonthDiff={'>=$(vZeitraum12Monate)<=$(vAktuellerMonat)'},
Kat={'Leere'}>}Daten_D)/sum({$<MonthDiff={'>=$(vZeitraum12Monate)<=$(vAktuellerMonat)'},
Kat={'Gesamtzahl Vorgang & Recherche'}>}Daten_D)),MonthNumber),MonthNumber),MonthNumber)
My problem is, that the intercept shown in textbox is not the same value like the value shown through
QV inbuild Trendline.
I suppose that QV is calculating from the first available data point. And since i filtered the dimension to the last
12 values QV is still using the first datapoint as intercept value for my textboxvalue ?!?!
Any ideas how to get the correct value into the textbox?
I attached a sample qvd file!
Thanks for helping
Something along the lines discussed in this thread:
Matching linest_m and linest_b function to auto-generated trendline
edit: Oleg, it's not enough to just replace MonthNumber with MonthAsNumber, the axis needs to start from zero.
Hi Frank,
I have to admit, this was the longest I spent in a while on a community problem - the challenge was too annoying...
The problem is caused by the difference between the two Month fields that you are using in your calculations:
- The chart is using the field MonthAsNumber that's running between 1 and 12
- The text box expressions are using the field MonthNumber that's running between 84 and 95 (with the current selection )
Obviously, the Y-intercept of the two trend lines will be different (the second imaginary line has 83 more steps to go).
Once you replace MonthNumber with MonthAsNumber, everything goes back to normal.
As a side comment, you seem to have a lot of unnecessary clutter in your app - Expressions that need to be removed, duplicate AGGR() that seems to be redundant etc...
Cheers,
Oleg Troyansky
Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!
Something along the lines discussed in this thread:
Matching linest_m and linest_b function to auto-generated trendline
edit: Oleg, it's not enough to just replace MonthNumber with MonthAsNumber, the axis needs to start from zero.