Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_Hartmann
Master II
Master II

linest_b

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

swuehl
MVP
MVP

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.