14 Replies Latest reply: Feb 6, 2014 4:08 PM by Michael Solomovich

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

• ###### Re: Calculation of Range

Can i use class function

• ###### Re: Calculation of Range

Stratification with Rank(Aggr()) as a dimension

• ###### Re: Calculation of Range

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

• ###### Re: Calculation of Range

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

• ###### Re: Re: Calculation of Range

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

• ###### Re: Calculation of Range

Abhi,

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

• ###### Re: Calculation of Range

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

it looks like

20+,1,3,7..

• ###### Re: Calculation of Range

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.

• ###### Re: Calculation of Range

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

• ###### Re: Calculation of Range

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

• ###### Re: Calculation of Range

dual('20+',21) is helping my case..but can you explain me why using dual in this case?

• ###### Re: Calculation of Range

To sort by numeric value, the values must be numeric.  The '20+' is text, not a number.  The dual() function creates values that have both text and numeric values (hence "dual").  In your case, text value '20+' has numeric value 21.  So, when you sort by numeric, it follows 20, exactly as you want it.

Note: Some date/time values are numeric in QV without using dual().  Months Jan to Dec has numeric values 1 to 12.  Weekdays Mon to Sun has numeric values 0 to 6.

• ###### Re: Calculation of Range

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

• ###### Re: Calculation of Range

Can't help with the counts, sorry.  It is about the details of your application and calculations.  I hope you'll figure out.