Using LinEst_M and RangeSum to get slope of cumulative data

nerf13 — Wed, 21 Sep 2022 17:28:43 GMT

Hi there!

I'm working in Qlik Sense on a prototype expression for a KPI item that returns the slope for cumulative claims rate of a product by months in service. My production data and claims data to calculate the failure rate come from unrelated tables, so I use a synthetic dimension "Months in Service" to relate them. I also plot the data on a log scale, so I transform "Months In Service" using log() for each month 1,2,3...etc In a straight table, RangeSum & Above functions return the cumulative failure rate by Months In Service, but I when I try to get the slope in my KPI by adding LinEst_M to those expressions, Qlik just returns 0. As a test, I checked using LinEst_M for the failure rate data without accumulating with RangeSum and it works. Does anyone know if this is just a syntax issue, or is it not possible to use RangeSum inside a LinEst expression? Hope someone can help me solve it - thanks!!!

A simplified version of my data looks like this:

Example Data for prototype expression

This expression works (not cumulative):

=sum(LinEst_M(total

// "Y" - Claims Rate
Pick(Match(ValueLoop(1,4,1),1,2,3,4),
.02
,
.05
,
.04
,
.03
)
// "X" - Months in Service (log Scale)
, Pick(Match(ValueLoop(1,4,1),1,2,3,4),

log(1)
,
log(2)
,
log(3)
,
log(4)
)))

This one (cumulative) does not! Any suggestions how to fix it, or is it a "no go" in Qlik sense for some reason?

=sum(LinEst_M(total

//"Y" - Cumulative Claims Rate
RangeSum(Above(Pick(Match(ValueLoop(1,4,1),1,2,3,4),

.02
,

.05
,

.04
,

.03

), 0, RowNo()))

//"X" - Months in Service (log Scale)
, Pick(Match(ValueLoop(1,4,1),1,2,3,4),

log(1)
,

log(2)
,

log(3)
,

log(4)

)))

Re: Using LinEst_M and RangeSum to get slope of cumulative data

nerf13 — Wed, 21 Sep 2022 17:33:07 GMT

I forgot to mention that it is not practical for me to do the RangeSum in advance (like loading in a script) right now, I'm hoping to resolve it within the expression!

topic Using LinEst_M and RangeSum to get slope of cumulative data in App Development

Using LinEst_M and RangeSum to get slope of cumulative data

Re: Using LinEst_M and RangeSum to get slope of cumulative data