Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linear Regression (linest_m & linest_b)

Hi everyone,

I've been trying to figure out how to get a linear regression line as an expression in QlikView and I think I've figured out the major part, but for some reason my expression is offset and I can't seem to figure out why.

This is the expression:

(

  LINEST_M({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}>}Revenue),

  Date

  ),Date

  )

  *

  num(Only({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, Revenue={'>0'}>}Date)-num('$(vAlert.Date.Min)')+1)

)

+

LINEST_B({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}>}DISTINCT Revenue),

  Date

  ),Date

)

And it's output is this:

screenshot_2016-06-03.PNG

Can anyone explain what's causing the offset? I've tried only using distinct values and small tweaks in the set expression, but no result.

Thanks in advance!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe have a look at

Re: Matching linest_m and linest_b function to auto-generated trendline

Or try

(

  LINEST_M({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}>}Revenue),

  Date

  ),Date

  )

  *

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

)

+

LINEST_B({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}>}DISTINCT Revenue),

  Date

  ),Date

)

View solution in original post

2 Replies
swuehl
MVP
MVP

Maybe have a look at

Re: Matching linest_m and linest_b function to auto-generated trendline

Or try

(

  LINEST_M({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}>}Revenue),

  Date

  ),Date

  )

  *

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

)

+

LINEST_B({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}, Revenue={'>0'}>}TOTAL

  Aggr(

  sum({<Date={">=$(vAlert.Date.Min)<=$(vAlert.Date.Max)"}, [System Key]={'Hive|APRT- CL'}>}DISTINCT Revenue),

  Date

  ),Date

)

Not applicable
Author

Thanks swuehl!

I was looking at that for ages!