
1. Re: Combine values which are below the limit?
Jim Wahl Feb 26, 2013 4:59 AM (in response to Himanshu Modak)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 5if 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. Rightclick on pill > Sort > Sort order > Descending; Sort by > Field > Sales (SUM)
3.2 Add Display rank (top n) to Rows. Rightclick 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), rightclick > Compute using > Product
3.5 Rightclick parameters > Show parameter control
3.6 Hide the null Product name; Right click on parameters > Show parameter control; Rightclick Display rank (top n) > uncheck Show Header; Rightclick 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 rightclick on each table calc pill > Compute using > Product.

TopN_v3.twbx.zip 361.3 KB


2. Re: Combine values which are below the limit?
Himanshu Modak Feb 28, 2013 2:03 AM (in response to Jim Wahl)Thanku so much Jim.