topic Renewal Rate in QlikView

Renewal Rate

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

Hi All,

I have a requirement to calculate renewal rate for the year 2016, 2017 and 2018.

Can you please help me to achieve this.

Many Thanks

Re: Renewal Rate

shiveshsingh — Thu, 10 Jan 2019 09:51:58 GMT

What is the formula to calculate the Renewal rate?

Re: Renewal Rate

karthikoffi27se — Thu, 10 Jan 2019 10:44:03 GMT

The formula for that is say for example in 2016 there are 100 policies and 2018 there are 150 policies out of 150 policies I got 70 as renewal and 80 as New policies, so the renewal rate is 70/100 = 70%.

Hope this helps.

Re: Renewal Rate

MohammedMustaq — Thu, 10 Jan 2019 13:23:47 GMT

Hi,

Hope this solve!

=Sum({<PolicyType={"Renewal"},Year={"$(vMaxYear)"}>}Policies)
/
Sum({<PolicyType={"New"},Year={"<$(vMaxYear)"}>}Policies)

=Sum({<PolicyType={'Renewal'},Year={"$(vMaxYear)"}>}Policies)
/
Sum({<PolicyType={'New','Renewal'},Year={"<$(vMaxYear)"}>}Policies)

vMaxYear=Max(Year)

Thanks

Mustaq

Re: Renewal Rate

shiveshsingh — Fri, 11 Jan 2019 05:07:44 GMT

How are you defining the new and existing policy? is there a flag?

Can you share some sample data to work upon?

Re: Renewal Rate

haribabugv — Fri, 11 Jan 2019 06:43:21 GMT

Hello Karthik

May be you can check this file for the calculations.

You can also do by using peek function in the script.