Calculation of Range

[#1]

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

[Anonymous · Accepted solution]

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

[#3]

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

[Anonymous]

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.

[#5]

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

[Anonymous]

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