topic Linear Regression (linest_m & linest_b) in QlikView

Linear Regression (linest_m & linest_b)

Fri, 03 Jun 2016 12:30:50 GMT

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!

Re: Linear Regression (linest_m & linest_b)

swuehl — Fri, 03 Jun 2016 13:07:36 GMT

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

)

Re: Linear Regression (linest_m & linest_b)

Fri, 03 Jun 2016 13:08:51 GMT

Thanks swuehl!

I was looking at that for ages!