Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Check enclosed file... I have considered that your date format is D/M/YYYY.
Check enclosed file... I have considered that your date format is D/M/YYYY.
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.