Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Positive/negative result from trendline

Hello folks,

I have a couple of charts with trendlines. Of course I can see whether the trendline is positive or negative by my eye, but I also want to visualize it as texts "Positive" / "Negative" depending of the trendline's direction.

My equations that has a positive trend has positive y values: y = 16109x + 9.3451e+005...

And my equations that has a negative trend has negative y values: y = -41282x + 1.5474+006...

My problem is that these equations are built-in QlikView calculations, which I activate through the diagram proporties. Does anybody know how to access the calculation of the trend's direction (y value) so that I can show it in a text object.

Thanks in advance.

Best,

Filip

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Check out the linest_m function to calculate the slope of your linear regression:

From the HELP:

linest_m ([{set_expression}][ distinct ] [total [<fld {,fld}>] ]y-expression, x-expression[, y0 [, x0 ]])

returns the aggregated m value (slope) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in x-expression and y-expression iterated over the chart dimension(s). Text values, null values and missing values in any or both pieces of a data-pair will result in the entire data-pair to be disregarded.

An optional valye y0 may be stated forcing the regression line to pass through the y-axis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0 are stated, a single data pair will do.

This function has the same limitations for nested aggregation as the avg([{set_expression}] [ distinct ] [ total [<fld { , fld } >]] expression) function. The linest_m function supports Set Analysis and the total qualifier in the same way as the avg([{set_expression}] [ distinct ] [ total [<fld { , fld } >]] expression) function.

Examples:

linest_m( Y, X )     

linest_m( A/B, X*Y/3 )    

linest_m( total Y, X )    

linest_m( total <Z> Y, X)    

linest_m( Y, X, 0 )    

linest_m( Y, X, 1, 1 )     

View solution in original post

3 Replies
Anonymous
Not applicable
Author

My problem is that these equations are built-in QlikView calculations, which I activate through the diagram proporties. Does anybody know how to access the calculation of the trend's direction (y value) so that I can show it in a text object.

Sorry, but I don't know what this means. Do you mean that the expression is set up somewhere other than in the chart properties ?

You could perhaps add another expression in a combo chart, test for increases or decreases in order to say something like 'Up' or 'Down', make sure each means of display (Bar, Line and Symbol) is deselected and then tick the 'Text on Axis' checkbox.

Jonathan

swuehl
MVP
MVP

Check out the linest_m function to calculate the slope of your linear regression:

From the HELP:

linest_m ([{set_expression}][ distinct ] [total [<fld {,fld}>] ]y-expression, x-expression[, y0 [, x0 ]])

returns the aggregated m value (slope) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in x-expression and y-expression iterated over the chart dimension(s). Text values, null values and missing values in any or both pieces of a data-pair will result in the entire data-pair to be disregarded.

An optional valye y0 may be stated forcing the regression line to pass through the y-axis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0 are stated, a single data pair will do.

This function has the same limitations for nested aggregation as the avg([{set_expression}] [ distinct ] [ total [<fld { , fld } >]] expression) function. The linest_m function supports Set Analysis and the total qualifier in the same way as the avg([{set_expression}] [ distinct ] [ total [<fld { , fld } >]] expression) function.

Examples:

linest_m( Y, X )     

linest_m( A/B, X*Y/3 )    

linest_m( total Y, X )    

linest_m( total <Z> Y, X)    

linest_m( Y, X, 0 )    

linest_m( Y, X, 1, 1 )     

Anonymous
Not applicable
Author

Thanks alot!

The linest_m function was exactly what I needed.