topic Re: How to create a benchmark expression in QlikView

How to create a benchmark expression

Anonymous — Fri, 24 Jul 2015 06:46:15 GMT

Hi guys,

I want to create a straight/pivot table where the values are show in this manner.

Agent Name	Agent Freq	Branch Freq	Company Freq
Agent A	2%	3%	7%
Agent B	3%	4%	7%

Basically, the agent freq is the actual freq for the agent. The branch freq is the total branch freq based on the selected agent branch located and Company is total whole data freq.

I managed to get Agent(straight formula) and Company( using set identifier 1). But i could'nt get the Branch Freq where the numbers need to be based on the selected agent branch. E.g. Agent A is based in Branch US and agent B is Branch Europe. So 3% and 4% above is actually the average freq for US and Europe.

Appreciate you guys assistance.

Re: How to create a benchmark expression

jonathandienst — Fri, 24 Jul 2015 06:55:04 GMT

Your requirement is not totally clear, but I think you need qualified totals of the form:

Sum(total <company, branch> value)

Play around with the dimension fields that you wish to ignore for the branch frequency.

Re: How to create a benchmark expression

Anonymous — Fri, 24 Jul 2015 07:24:38 GMT

Hi Jonathan,

not quite sure what you meant. However this is my code for the agent freq and company freq.

Company

count({1<CMOVPF_BATCTRCDE = {T421}>} total if(Own_Damaged_Count > 0,CLAIMNUMBER))/sum({1<VEHICLECOVERTYPE = {'COMPREHENSIVE'}>}total Earn_Count)

Agent

count({<CMOVPF_BATCTRCDE = {T421}>} if(Own_Damaged_Count > 0,CLAIMNUMBER)) / sum({<VEHICLECOVERTYPE = {'COMPREHENSIVE'}>} Earn_Count)

R u saying i should sum(total <company,branch fields> the above codes ?

Re: How to create a benchmark expression

Kushal_Chawda — Fri, 24 Jul 2015 07:30:36 GMT

it will be a more clear if you will post sample file with expected output

Re: How to create a benchmark expression

Anonymous — Fri, 24 Jul 2015 10:06:02 GMT

Hi Kush,

please find below sample data.

Data
Policy no	Agent	Branch	No of Claims
A1	AGENT 1	US	1
A2	AGENT 2	EUROPE	1
A3	AGENT 3	US	0
A4	AGENT 4	US	0
A5	AGENT 5	US	1
A6	AGENT 6	EUROPE	0
A7	AGENT 7	ASIA	0
A8	AGENT 8	ASIA	0
A9	AGENT 9	EUROPE	1
A10	AGENT 10	US	0
B1	AGENT 1	US	0
B2	AGENT 2	EUROPE	1
B3	AGENT 3	US	0
B4	AGENT 4	US	2
B5	AGENT 5	US	1
B6	AGENT 6	EUROPE	0
B7	AGENT 7	ASIA	0
B8	AGENT 8	ASIA	1
B9	AGENT 9	EUROPE	0
B10	AGENT 10	US	1

Expected result
Agent Name	Agent freq	branch	Company
agent 1	50%	60%	50%
agent 2	100%	50%	50%
agent 7	0%	25%	50%

The frequency is based on sum of claim/ count of policies.

Re: How to create a benchmark expression

Kushal_Chawda — Fri, 24 Jul 2015 11:51:06 GMT

Please see the attached.

Only issue here is, when you will select any agent from listbox, Branch Freq will change as it will aggregate the data based on Agent. You cannot exclude the selection in expression as dimension is used in chart.

Re: How to create a benchmark expression

Anonymous — Mon, 27 Jul 2015 02:15:08 GMT

Where is the attached?

Re: How to create a benchmark expression

Anonymous — Mon, 27 Jul 2015 06:10:34 GMT

Ok i see it now. There is no possible ways to actually show if i selected an agent(in list box). What if i did'nt use agent as the dimension? Is there any other way?