topic Re: linest_b in QlikView

linest_b

Frank_Hartmann — Thu, 28 Jan 2016 15:38:22 GMT

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

Re: linest_b

Oleg_Troyansky — Thu, 28 Jan 2016 17:52:37 GMT

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!

Re: linest_b

swuehl — Thu, 28 Jan 2016 18:01:52 GMT

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.