Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
mov
Esteemed Contributor III

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

14 Replies
Not applicable

Re: Calculation of Range

Can i use class function

michael_gardner
Contributor III

Re: Calculation of Range

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

Stratification with Rank(Aggr()) as a dimension

Employee
Employee

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

Not applicable

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

Employee
Employee

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

mov
Esteemed Contributor III

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


Not applicable

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

mov
Esteemed Contributor III

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.


Not applicable

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

Community Browser