Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET:
**REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: equivalent for excel SLOPE function

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

lukas_sokol

Contributor II

2015-11-17
07:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

equivalent for excel SLOPE function

Hi,

Is there any equivalent for Excel SLOPE function.

https://support.office.com/en-us/article/SLOPE-function-11fb8f97-3117-4813-98aa-61d7e01276b9

Regards

Łukasz

1,445 Views

1 Solution

Accepted Solutions

swuehl

MVP

2015-11-17
12:59 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

LOAD * INLINE [

Month, Value

1,94%

2,99%

3,99%

];

In the frontend, create a text box with expression

=LINEST_M( Value, Month)

It returns 0.025, i.e. 2.5% which seems reasonable to me.

1,173 Views

4 Replies

swuehl

MVP

2015-11-17
08:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Look into the linest_m function:

**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 )

lukas_sokol

Contributor II

2015-11-17
12:53 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you for the information and examples.

I have a question how to write an expression.

I have a measure "hits%", and I wanna determine the slope for 3 months.

For Example I have values 94.% 99% 99%, and months numbered 1,2,3.

In excel I get slope = 3%

1,173 Views

swuehl

MVP

2015-11-17
12:59 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

LOAD * INLINE [

Month, Value

1,94%

2,99%

3,99%

];

In the frontend, create a text box with expression

=LINEST_M( Value, Month)

It returns 0.025, i.e. 2.5% which seems reasonable to me.

1,174 Views

lukas_sokol

Contributor II

2015-11-18
05:11 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thank you for your help.

I have one small problem.

I have a lot of "values" for various periods.

I want to count linest_m for 3 months back

i try to use

LINEST_M( sum({<Month={'>=$(=only(Month)-2)<=$(=only(Month))'}>}hits) , Month)

or

LINEST_M( sum(aggr(rangesum(above(total sum({<Month=>}hits),0,3)),Month)), Month)

but it's doesn't work

1,173 Views