topic Calculation of Range in QlikView

Calculation of Range

Wed, 05 Feb 2014 19:22:55 GMT

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

Wed, 05 Feb 2014 19:58:01 GMT

Can i use class function

Re: Calculation of Range

Anonymous — Wed, 05 Feb 2014 20:06:30 GMT

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

Stratification with Rank(Aggr()) as a dimension

Re: Calculation of Range

hic — Wed, 05 Feb 2014 20:22:22 GMT

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

Thu, 06 Feb 2014 17:30:48 GMT

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

hic — Thu, 06 Feb 2014 18:02:01 GMT

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

Anonymous — Thu, 06 Feb 2014 18:19:16 GMT

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

Thu, 06 Feb 2014 20:01:05 GMT

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

Anonymous — Thu, 06 Feb 2014 20:05:40 GMT

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

Thu, 06 Feb 2014 20:11:29 GMT

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

Anonymous — Thu, 06 Feb 2014 20:16:19 GMT

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

Thu, 06 Feb 2014 20:27:52 GMT

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

Re: Calculation of Range

Anonymous — Thu, 06 Feb 2014 20:35:12 GMT

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

Thu, 06 Feb 2014 20:55:05 GMT

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

Re: Calculation of Range

Anonymous — Thu, 06 Feb 2014 21:08:10 GMT

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