Linear Regression (linest_m & linest_b)

[#1]

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:

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!

[swuehl · Accepted solution]

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

)

[#3]

Thanks swuehl!

I was looking at that for ages!