Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

1#INF linear regression

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!

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

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

View solution in original post

9 Replies
Clever_Anjos
Employee
Employee

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

Not applicable
Author

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:

    

SysKeyDate2011-07-042011-07-052011-07-062011-07-07
PageviewsHive|ADA - Aggr -1,#IND-1,#IND-1,#IND-1,#IND
PageviewsHive|ADA - Aggr RTB -1,#IND-1,#IND-1,#IND-1,#IND
PageviewsHive|ADA - CL ----
PageviewsHive|ADA - CL RTB ----
PageviewsHive|ADA - RTB -1,#IND-1,#IND-1,#IND-1,#IND
Clever_Anjos
Employee
Employee

Would you mind sharing a sample of your app?

Preparing examples for Upload - Reduction and Data Scrambling

Not applicable
Author

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

screenshor_2016-06-06.PNG

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

Should I check the last expression, right?

Not applicable
Author

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

Clever_Anjos
Employee
Employee

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

Not applicable
Author

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

Good to read that, Maarten!