Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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)
)))
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!