topic Re: 1#INF linear regression in QlikView

1#INF linear regression

Mon, 06 Jun 2016 09:03:22 GMT

Hi everybody!

I'm currently busy with some linest_m and linest_b expressions, but they're giving some strange output. This is the current expression:

num(

alt(

(

//Slope

LINEST_M(TOTAL <[SysKey]> {<Date={">=$(vAlert.Min)<=$(vAlert.Max)"}, [Pageviews]={'>0'}, Flag={0}>}

Aggr(

sum({<Date={">=$(vAlert.Min)<=$(vAlert.Max)"}, Flag={0}>}[Pageviews]),

Date, [SysKey]

)

Date, [SysKey]

)

Only({<Date={">=$(vAlert.Min)<=$(vAlert.Max)"}, Flag={0}>}Date)

)

//Offset

LINEST_B(TOTAL <[SysKey]> {<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [Pageviews]={'>0'}, Flag={0}>}

Aggr(

sum({<Date={">=$(vAlert.Min)<=$(vAlert.Max)"}, Flag={0}>} [Pageviews]),

Date

)

Date

)

,0)

)

When I tried more or less the same expression in QlikView with a single dimension it worked without issues, but with the added second dimension it's now returning '1 #INF'. Does anybody have an idea why this happens?

Many thanks!

Re: 1#INF linear regression

Clever_Anjos — Mon, 06 Jun 2016 12:19:49 GMT

When you have more than one dimension, your expression will be evaluated for each distinct combination of Dim1, Dim2,...Dimn,

Maybe your expression should be rewritten using LINEST_M(TOTAL <[System Key], [Other Dimension]>

Re: 1#INF linear regression

Mon, 06 Jun 2016 12:45:34 GMT

Hi Clever,

The result should be the linear regression of Pageviews over Date, so when I do that over one dimension (which is Date) everything is fine. But when I introduced the second dimension the numbers didn't correspond, so I figured that was because of the second dimension, in this case [SysKey]. So I changed the Aggr and Linest functions to accomodate that dimension, but this is my result:

	SysKey	Date	2011-07-04	2011-07-05	2011-07-06	2011-07-07
Pageviews	Hive\|ADA - Aggr		-1,#IND	-1,#IND	-1,#IND	-1,#IND
Pageviews	Hive\|ADA - Aggr RTB		-1,#IND	-1,#IND	-1,#IND	-1,#IND
Pageviews	Hive\|ADA - CL		-	-	-	-
Pageviews	Hive\|ADA - CL RTB		-	-	-	-
Pageviews	Hive\|ADA - RTB		-1,#IND	-1,#IND	-1,#IND	-1,#IND

Re: 1#INF linear regression

Clever_Anjos — Mon, 06 Jun 2016 13:36:55 GMT

Would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

Re: 1#INF linear regression

Tue, 07 Jun 2016 09:58:52 GMT

Thanks for the link Clever!

This is a sample of the application I'm trying to build. I'm trying to identify points in a dataset where a metric falls outside of a certain bandwith, in this case a metric like Revenue compared to it's average or linear regression line and a bandwith of +/- times it's standard deviation. the comparison to the average works, but getting a correct output in the orange heatmap doesn't work with linear regression. That's why I'm trying to rewrite my linear regression expression to work with the field [System Key].

I'm trying to get a true or false output in the orange heatmap in a similar way this line chart identifies points that fall outside of the visible bandwith.

Re: 1#INF linear regression

Clever_Anjos — Wed, 08 Jun 2016 11:30:25 GMT

Should I check the last expression, right?

Re: 1#INF linear regression

Wed, 08 Jun 2016 11:32:37 GMT

Hi Clever! I'm looking for the right expression under the variable $(eAlert.KPI.LR.SysKey(Revenue)).

Re: 1#INF linear regression

Clever_Anjos — Wed, 08 Jun 2016 12:09:18 GMT

Checking the linest_b/m documentation and your expression

LINEST_B(TOTAL <[System Key]> {<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [$1]={'>0'}, _RAFlag={0}, [Reload ID]={"$(=MaxString([Reload ID]))"}>}

Aggr(

sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, _RAFlag={0}, [Reload ID]={"$(=MaxString([Reload ID]))"}>} [$1]),

Date, [System Key]

)

Date, [System Key]

I could see that the function expects a pair of lists (y-value, aggr in your case and x-value, date in your case). The 3rd and 4rd values can´t be a list, they are supposed to be atomic values

Re: 1#INF linear regression

Wed, 08 Jun 2016 12:21:06 GMT

Thanks Clever!

I just started using linest functions so I already was wondering why my expression didn't work, but you're explanation clarifies it. I finally got it working! Thanks!

Re: 1#INF linear regression

Clever_Anjos — Wed, 08 Jun 2016 13:18:01 GMT

Good to read that, Maarten!