Have you attempted using the count or distinct count? Add a sample set of the data so that a community "jedi" can mock up a solution.
Senior Business Consultant
Attached you will find an excel file with some test data. In the first sheet i have the contracts table and the date range in which each contract is active. In the second sheet i have the claims table which shows all the claims for all of the contracts (Key= CONTRACT ID) and the event date of the claim. What i want to do is get the frequency ratio based on the event month of the claims.
So for example for February 2012, i have 4 claims that happened (total claims, i don't care about distinct contracts) but only 2 distinct contracts where active at that month (Contracts with ID = 1 and 2). So the frequency for that month should be equal to: 4/2 = 200%.
For all of the months that there were no claims the frequency should by equal to 0. For September 2013 there was only one claim but there were 4 active contracts for that month. So the frequency for that month should be equal to: 1/4 = 25%.
I hope i explained things enough.
Thank you in advance for your help.
Did you have a look at my test data?