Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
)
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
)
Thanks swuehl!
I was looking at that for ages!