topic Re: Need Help to create roll rate line chart from above and before function in QlikView

Need Help to create roll rate line chart from above and before function

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Basically, I want to create Roll rate/Flow rate/Delinquency rate from my data.

It was very easy on Excel (as attached sample), but on qlikview I was only able to show the flow rate number correctly using pivot chart

with expressions:

=sum(TotalOSPrincipal)/above(before(SUM(TotalOSPrincipal)))

when I convert the pivot chart, it show "no data to display", I think above and before function is not suitable for this case, any idea or alternative to get the line chart?

FYI, to get the flow rate number I use formula as below :

then easily create line chart based on roll rate number.

thanks in advance...

Re: Need Help to create roll rate line chart from above and before function

sunny_talwar — Fri, 27 Jul 2018 14:54:32 GMT

Try this expression

=Sum(TotalOSPrincipal)/Aggr(Above(Aggr(Above(Sum(TotalOSPrincipal)), BUCKET1, PERIODE_REPORTING)), PERIODE_REPORTING, BUCKET1)

Re: Need Help to create roll rate line chart from above and before function

sunny_talwar — Fri, 27 Jul 2018 14:56:57 GMT

Or this

=Sum(TotalOSPrincipal)/Only({<BUCKET1, PERIODE_REPORTING>}Aggr(Above(Only({<BUCKET1, PERIODE_REPORTING>}Aggr(Above(SUM({<BUCKET1, PERIODE_REPORTING>}TotalOSPrincipal)), BUCKET1, PERIODE_REPORTING))), PERIODE_REPORTING, BUCKET1))

Re: Need Help to create roll rate line chart from above and before function

Anonymous — Mon, 30 Jul 2018 04:23:28 GMT

Hi Sunny,

Thank You very much, it's work flawlessly!

I took so many time to resolve this using set analysis, but suddenly you solved this using Aggregation and above function.

I should learn again about those function, to know better when to use it.

Have a nice day...

Re: Need Help to create roll rate line chart from above and before function

sunny_talwar — Mon, 30 Jul 2018 17:25:24 GMT

Just remember that set analysis is evaluated once per chart, so it can't be used for scenarios like these where you expecting a different value from set analysis every dimension. In these scenarios, Inter-Record Functions come in handy

Re: Need Help to create roll rate line chart from above and before function

willianvbuss — Mon, 30 Mar 2020 17:00:38 GMT

Sunny.

I'm trying in qlik sense this same expression, because i'm with the same problem to calculate the roll rate.

But it's not working... I tryed this:

=Sum(SomaSaldo)/Aggr(Above(Aggr(Above(Sum(SomaSaldo)), InfCarteiraMensal_AnoMes, FaixadeAtraso_0a180)), FaixadeAtraso_0a180, InfCarteiraMensal_AnoMes)

Can you help me?