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: 
Not applicable

Calculation of Range

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....

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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))....

View solution in original post

14 Replies
Not applicable
Author

Can i use class function

Anonymous
Not applicable
Author

The correct answer in the below link may lead you in the right direction.

Stratification with Rank(Aggr()) as a dimension

hic
Former Employee
Former Employee

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

Not applicable
Author

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...

hic
Former Employee
Former Employee

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

Anonymous
Not applicable
Author

Abhi,

I just thought that this is very similar to your request, take a look: http://community.qlik.com/message/463656


Not applicable
Author

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

it looks like

20+,1,3,7..

Anonymous
Not applicable
Author

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.


Not applicable
Author

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