Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Renewal Rate Calculation

Please see data example below (left) and Straight table needed below (right). I need to show Renewals by Effective_Date in 2015 (I can get this), and show the respective renewal rate.  Renewal Rate = Renewals / Renewal Base, where Renewal Base is the number of contracts that expired (Expiration_Date) on the respective Effective Date.


Thanks for any assistance you can provide.


Data

Straight Table Needed

Contract_ID

Effective_Date

Expiration_Date

Period (Renewal Effective Mo.)

Renewals

Renewal Base

Renewal Rate

1

1/1/2014

1/1/2015

1/1/2015

2

2

100%

2

1/1/2014

1/1/2015

1/2/2015

1

2

50%

3

1/2/2014

1/2/2015

1/3/2015

2

2

100%

4

1/2/2014

1/2/2015

2/1/2015

2

2

100%

5

1/3/2014

1/3/2015

2/2/2015

2

2

100%

6

1/3/2014

1/3/2015

2/3/2015

1

2

50%

7

2/1/2014

2/1/2015

Total

10

12

83%

8

2/1/2014

2/1/2015

9

2/2/2014

2/2/2015

10

2/2/2014

2/2/2015

11

2/3/2014

2/3/2015

12

2/3/2014

2/3/2015

13

1/1/2015

1/1/2016

14

1/1/2015

1/1/2016

15

1/2/2015

1/2/2016

16

1/3/2015

1/3/2016

17

1/3/2015

1/3/2016

18

2/1/2015

2/1/2016

19

2/1/2015

2/1/2016

20

2/2/2015

2/2/2016

21

2/2/2015

2/2/2016

22

2/3/2015

2/3/2016

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Check enclosed file... I have considered that your date format is D/M/YYYY.

View solution in original post

2 Replies
MK_QSL
MVP
MVP

Check enclosed file... I have considered that your date format is D/M/YYYY.

Not applicable
Author

Thank you Manish.  I was thinking that another date needed to be introduced, but was not sure how to do that.  I think I can make this approach work.