1#INF linear regression

[#1]

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!

[Clever_Anjos]

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]>

[#3]

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

[Clever_Anjos]

Would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

[#5]

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.

[Clever_Anjos]

Should I check the last expression, right?

[#7]

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

[Clever_Anjos · Accepted solution]

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

[#9]

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!

[Clever_Anjos]

Good to read that, Maarten!