Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I hope someone can help!
I am trying to predict the Accident Frequency of individual policies based on the average of all policies to date. I have split the data in to policy days and evaluated the frequency of a claim happening on a set policy day based on previous data:
1) For an individual Policy
Pol Cover Day | POLICY NUMBER | Accident Frequency |
1 | ABC | 0.00% |
2 | ABC | 0.00% |
3 | ABC | 100.00% |
4 | ABC | 0.00% |
5 | ABC | 0.00% |
6 | ABC | 0.00% |
7 | ABC | 0.00% |
8 | ABC | 50.00% |
9 | ABC | 0.00% |
10 | ABC | 0.00% |
11 | ABC | 0.00% |
12 | ABC | 0.00% |
13 | ABC | 0.00% |
14 | ABC | 0.00% |
15 | ABC | 0.00% |
Accident Frequency Calculation =
count(distinct(if(ClaimPolicyDay=PolCoverDay,Claim_Number)))/count(distinct(if(PolCoverDay<=PolicyDays2,POLICYNUMBER)))
where PolicyDays2 is the number of policy days that have passed so far (so in this example = 15)
2) The average over all policies to date
Pol Cover Day | Average Accident Frequency |
1 | 0.09% |
2 | 0.16% |
3 | 0.15% |
4 | 0.16% |
5 | 0.09% |
6 | 0.07% |
7 | 0.08% |
8 | 0.09% |
9 | 0.09% |
10 | 0.08% |
11 | 0.10% |
12 | 0.09% |
13 | 0.16% |
14 | 0.08% |
15 | 0.06% |
16 | 0.05% |
17 | 0.14% |
18 | 0.06% |
19 | 0.09% |
20 | 0.05% |
Average Accident Frequency Calculation =
count(distinct{<POLICYNUMBER=>} if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(distinct{<POLICYNUMBER=>}if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
so ignoring any selectios made on PolicyNumber
Ultimately I want the result to be the Actual Accident Frequency for a particular policynumber so far and then for the remaining policy days (so in this example 16-20), the Average Accident Frequency to show.
This works if I don't have POLICYNUMBER as a dimension in the chart, but when I do it just gives me values for all policynumbers.
I need PolicyNumber as a dimension.
Any help would be gratefully received!
if( not isnull(POLICYNUMBER),
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
)
Try adding this to the first table as "Average Accident Frequency":
count({<POLICYNUMBER=>} DISTINCT TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count({<POLICYNUMBER=>} DISTINCT TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
Hi Lucian,
Thank you for the speedy response. I have changed this slightly (see below) as it wouldn't accept the formula, but this works to a certain extent.
My only problem now is it shows all the Policy Numbers in the Dimensions rather than just the ones I have selected. Is there any way to do this?
i.e. if I had policynumbers ABC, 123, XYZ and I selected ABC, I would still want the Average Accident Frequency to be calculated over all three policy numbers, but only ABC to be shown as a Dimension in the chart.
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
count(DISTINCT TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(DISTINCT TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
Once again, thanks for your quick reply, but this only evaluates over the policy number selected.
.. rather than just the ones I have selected...
...this only evaluates over the policy number selected...
you lost me...
I need be able to select a policy number, so that only this policy number all Pol Cover Days show in the dimension (i.e. 1 to 365). I need then the Accident Frequency to take in to account the selection but not the Average Accident Frequency. Does this make sense?
if( not isnull(POLICYNUMBER),
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))
)
This works perfectly! Thank you so much for your help.
Lucian,
The only one slight problem I have now is this:
When I selected one or two Policy Numbers, the remaining Policy's show '-' for all expressions (exactly as I wanted), but I can't get rid of these extra policy numbers! I have selected 'Suppress Null Values' on the Dimensions tab and 'Suppress Missing' on the Presentation tab with no joy. If I select 'Suppress Zero Values', this gets rid of the extra Policy Numbers, but doesn't show all the 365 PolCoverDay's I need either as some of these are zero!
So close!
E.g.
POLICYNUMBER | PolCoverDay | Accident Frequency | Average Accident Frequency | Predicted Accident Frequency |
ABC | 1 | 0.00% | 0.09% | 0.00% |
ABC | 2 | 0.00% | 0.16% | 0.00% |
ABC | 3 | 0.00% | 0.15% | 0.00% |
ABC | 4 | 100.00% | 0.16% | 100.00% |
ABC | 5 | 0.00% | 0.09% | 0.00% |
ABC | 6 | 0.00% | 0.07% | 0.00% |
ABC | 7 | 0.00% | 0.08% | 0.00% |
ABC | 8 | 0.00% | 0.09% | 0.00% |
ABC | 9 | 0.00% | 0.09% | 0.00% |
ABC | 10 | 0.00% | 0.08% | 0.00% |
ABC | 11 | 0.10% | 0.10% | |
ABC | 12 | 0.09% | 0.09% | |
ABC | 13 | 0.16% | 0.16% | |
ABC | 14 | 0.08% | 0.08% | |
ABC | 15 | 0.06% | 0.06% | |
XYZ | 1 | - | - | - |
XYZ | 2 | - | - | - |
XYZ | 3 | - | - | - |
XYZ | 4 | - | - | - |
XYZ | 5 | - | - | - |
XYZ | 6 | - | - | - |
XYZ | 7 | - | - | - |
XYZ | 8 | - | - | - |
XYZ | 9 | - | - | - |
XYZ | 10 | - | - | - |
XYZ | 11 | - | - | - |
XYZ | 12 | - | - | - |
XYZ | 13 | - | - | - |
XYZ | 14 | - | - | - |
XYZ | 15 | - | - | - |