Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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 ?
it will be a more clear if you will post sample file with expected output
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.
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.
Where is the attached?
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?