Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to create a benchmark expression

Hi guys,

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

Agent NameAgent FreqBranch FreqCompany Freq
Agent A2%3%7%
Agent B3%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.

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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  ?

Kushal_Chawda

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

Anonymous
Not applicable
Author

Hi Kush,

please find below sample data.

  

Data
Policy noAgentBranchNo of Claims
A1AGENT 1US1
A2AGENT 2EUROPE1
A3AGENT 3US0
A4AGENT 4US0
A5AGENT 5US1
A6AGENT 6EUROPE0
A7AGENT 7ASIA0
A8AGENT 8ASIA0
A9AGENT 9EUROPE1
A10AGENT 10US0
B1AGENT 1US0
B2AGENT 2EUROPE1
B3AGENT 3US0
B4AGENT 4US2
B5AGENT 5US1
B6AGENT 6EUROPE0
B7AGENT 7ASIA0
B8AGENT 8ASIA1
B9AGENT 9EUROPE0
B10AGENT 10US1

   

Expected result
Agent NameAgent freqbranchCompany
agent 1 50%60%50%
agent 2100%50%50%
agent 70%25%50%

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

Kushal_Chawda

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.

Anonymous
Not applicable
Author

Where is the attached?

Anonymous
Not applicable
Author

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?