topic Re: Evaluating single field value against total average in QlikView

Evaluating single field value against total average

Thu, 26 Mar 2015 16:10:01 GMT

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!

Re: Evaluating single field value against total average

luciancotea — Thu, 26 Mar 2015 16:19:08 GMT

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))

Re: Evaluating single field value against total average

Thu, 26 Mar 2015 16:41:48 GMT

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))

Re: Evaluating single field value against total average

luciancotea — Thu, 26 Mar 2015 16:56:06 GMT

count(DISTINCT TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
/
count(DISTINCT TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))

Re: Evaluating single field value against total average

Thu, 26 Mar 2015 17:02:41 GMT

Once again, thanks for your quick reply, but this only evaluates over the policy number selected.

Re: Evaluating single field value against total average

luciancotea — Thu, 26 Mar 2015 17:05:14 GMT

.. rather than just the ones I have selected...

...this only evaluates over the policy number selected...

you lost me...

Re: Evaluating single field value against total average

Thu, 26 Mar 2015 17:10:57 GMT

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?

Re: Evaluating single field value against total average

luciancotea — Thu, 26 Mar 2015 17:22:16 GMT

if( not isnull(POLICYNUMBER),

   count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay=ClaimPolicyDay, Claim_Number))
    /
   count(DISTINCT{<POLICYNUMBER=>} TOTAL <PolCoverDay> if(PolCoverDay<=PolicyDays2,POLICYNUMBER))

)

Re: Evaluating single field value against total average

Fri, 27 Mar 2015 09:41:28 GMT

This works perfectly! Thank you so much for your help.

Re: Evaluating single field value against total average

Fri, 27 Mar 2015 11:01:20 GMT

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	-	-	-