Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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....
Try this:
if($(vfreqcount)<21,$(vfreqcount),dual('20+',99))....
Or maybe this, but it looks redundant:
if($(vfreqcount)<21,dual('$(vfreqcount)',$(vfreqcount)),dual('20+',99))....
Can i use class function
The correct answer in the below link may lead you in the right direction.
You can calculate the frequency with
Count(distinct CallID)
and if you want an upper limit, you can use e.g.
If(Count(distinct CallID)<=20, Count(distinct CallID), '20+')
Further, posting the same question in 20+ places on QlikCommunity, in unrelated discussion threads and in unrelated blog posts just irritates people, so that they are less inclined to answer you. Post the question in one place only.
Finally, if you can post a file with sample data, it would be a lot easier to help you.
HIC
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...
There seems to me as if there is a bracket missing... The if() inside the first aggr() is not closed.
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)
)
But I would strongly advice you to put such a calculation in the script instead... It very quickly becomes unmanageable. Read this blog post: http://community.qlik.com/blogs/theqlikviewblog/2014/02/03/the-key-to-heaven
HIC
Abhi,
I just thought that this is very similar to your request, take a look: http://community.qlik.com/message/463656
hey its working. thanks a ton. But it is displaying at the first before the frequency 1.
it looks like
20+,1,3,7..
The secret is in sorting.
Notice that I made dimension values "dual" - text and number. You can assign number like 99 to the "21+", and sort by numeric value.
I used this as my calculated dimension by setting the earlier calculated dimension into a variable vfreqcount
and used =if($(vfreqcount)<21,$(vfreqcount),'20+')....
But it is displaying 20+ at the start..how to get it to the end after 19