Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
karthikoffi27se
Contributor III

Renewal Rate

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

5 Replies
shiveshsingh
Honored Contributor

Re: Renewal Rate

What is the formula to calculate the Renewal rate?

karthikoffi27se
Contributor III

Re: Renewal Rate

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.

MohammedMustaq
Contributor

Re: Renewal Rate

 

Hi,

Hope this solve!


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

 

OR

 

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

 

vMaxYear=Max(Year)

 Thanks

Mustaq

shiveshsingh
Honored Contributor

Re: Renewal Rate

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

Can you share some sample data to work upon?

haribabugv
Contributor

Re: Renewal Rate

Hello Karthik

May be you can check this file for the calculations.

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

Renewal.JPG