Calculating Range for a frequency of calls!!

[#1]

Hey guys,

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

[marcus_sommer]

If your table not really interact with any selections what means the table is static then you could rank the frequencies within the load-script. In GUI you could use class() as dynamic dimension with a dynamic class-range. This is a quite complex task and official this isn't possible but it worked with some restrictions - but it could be easier to take two/several tables for this or to persuade your manager that he want to look on the data in a different way ... good luck.

- Marcus

[rwunderlich]

Can you post your Calculated Dim expression and we can suggest how to modify it?

-Rob

[#4]

I have used this calculated dimension to calculate the frequency

if(isnull(ENTITY_DISPLAY_NAME_VOD_C),aggr(if(count(v_ID_no_Parent)<>0,Count(v_id_no_Parent),[$(vGrp)],PRIMARY_ACCOUNT_C),aggr(if(Count(v_ID_no_Parent)<>0,count(v_ID_no_Parent)),[$(vGrp)],ENTITY_DISPLAY_NAME_VOD_C))

vgrp is for cyclic group ....v_id_no_Parent indicates calls ...rest is to calculate the right HCP count ....we are basically calculating frequencies of calls per territory..

Please help me with this...I need the solution asap...Can you people just modify the code according to the requirement...i tried...its not working...

[Gysbert_Wassenaar]

Try putting the expression in your calculated dimension in a variable, e.g. vFreqCount. Don't put an = sign in front of it in the variable. Then use as calculated dimension: =if( $(vFreqCount) < 21, $(vFreqCount), '20+')

[#6]

hey its working. thanks a ton. But it is displaying at the first before the frequency 1.

it looks like

20+,1,3,7...

[Gysbert_Wassenaar]

Ok, try =if( $(vFreqCount) < 21, $(vFreqCount), dual('20+',21))

[#8]

Can you explain why you using dual in this case?

[Gysbert_Wassenaar]

It creates a value with both a text format and a numeric value. That way you can sort the calculated dimension by number and the 20+ value should appear as last value.

[#10]

I am still getting wierd values for some territories. Call count is not matching....when i was testing the app