2 Replies Latest reply on Feb 28, 2013 2:03 AM by Himanshu Modak

# Combine values which are below the limit?

Hi,

I need to combine values which are below the limit to "Other".

There are two fields in my table City and Mobile Connection

Ex.

City     Mobile Connection

ABC     200

BCD     40

CDE     60

DEF     80

EFG     150

FGH     90

Max connection value is 200 of ABC, so 40 % of 200 is 80 which becomes lower limit and all the values which comes below this limit were combined in "Other". i.e. the pie chart text data view will be

City     Mobile Connection

ABC      200

EFG      150

FGH      90

DEF      80

Other     100

Thanks,

Himanshu

• ###### 1. Re: Combine values which are below the limit?

Hi Himanshu,

This is rather difficult to do, since you have to conditionally aggregate a dimension based on the value of a measure.

The basic trick I used below was to order the dimension (in my example, product) by the measure (SUM(sales) descending) that I'm aggregating on. If I want just the top 3 values, then I can use the table function INDEX() to select the first three products and group everything else as "other."

Your example is a little more difficult, since you need to calculate this top n value based ona sales threshold. I used another table calc for this (step 5.1 below).

The attached worksheet and instructions below, allow you to switch between a specified top n value and a calculated top n value. Of course, you could pick one and simplify things a bit. Let me know if you have any questions (it was much more involved than I first thought it would be). And maybe someone else will chime in with an easier solution.

Jim

Instructions for attached view:

Part I -- Create view based on specified Top N

1. Create 2 parameters:

1.1 Show all values > Data type boolean, default to False

1.2 Show top n products (Int), default to say 5---if you set min and max values, you can use a slider in the parameter control.

2. Create 4 calculated fields:

2.1 In top n -- shows the top n values based on INDEX() of table calculation for field (which we'll add later). For example: compute using Product > Order by SUM(Sales) descending.

INDEX() <= [Show top n products]

2.2 Products (top n)

IF [In top n] OR [Show all values] THEN ATTR([Product])

ELSEIF INDEX() == [Show top n products]+1

THEN STR(SIZE() - [Show top n products]) + " others" END

2.3 Display value (top n) -- aggregates at either product or "other" level

IF [In top n] OR [Show all values] THEN SUM([Sales])

ELSEIF INDEX() == [Show top n products]+1

THEN WINDOW_SUM(SUM([Sales]),0,LAST()) END

2.4 Display rank (top n) -- used to push "other" to bottom of sort

IF [In top n] THEN INDEX() ELSE [Show top n products]+1 END

3. Create View

3.1 Add Product to level of detail. Right-click on pill > Sort > Sort order > Descending; Sort by > Field > Sales (SUM)

3.2 Add Display rank (top n) to Rows. Right-click on pill > change to Discrete if necessary.

3.3 Add Products (top n) to Rows, Display value (top n) to Columns, In top n to Color shelf.

3.4 For each of the table calc pills (everything put Product in Level of Detail shelf), right-click > Compute using > Product

3.5 Right-click parameters > Show parameter control

3.6 Hide the null Product name; Right click on parameters > Show parameter control; Right-click Display rank (top n) > uncheck Show Header; Right-click on row header > Hide field labels for rows

Part II: Computing top n based on value

4. Create 2 params:

4.1 Show values above \$ (Int), ... enter range if you want a slider

4.2 Display Top N or Above value? (String) > List, add values Top n and Above value (\$) --- used to switch between specified and calculated top n values

5. Create 2 calculated fields

5.1 Z - Calculate top n based on value --- Since the products are ordered by sum of sales (descending), we want the largest index() where

the sum of sales for the product is > than the specified value.

WINDOW_MAX(IF SUM(Sales) > [Show values above \$] THEN INDEX() ELSE NULL END)

5.2 Z - Show top n products --- Used to support both top n (parameter) and by value (calculated top n)

IF [Display Top N or Above value?] == 'Top n' THEN [Show top n products]

ELSE INT([Z - Calculate top n based on value]) END

6. Replace the parameter [Show top n products] with the calculated field [Z - Show top n products] in all four calculated fields from part I.

7. Add new parameters. When you first select Above value (\$) from the Display parameter, you'll need to right-click on each table calc pill > Compute using > Product.

1 of 1 people found this helpful
• ###### 2. Re: Combine values which are below the limit?

Thanku so much Jim.