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

    Combine values which are below the limit?

    Himanshu Modak

      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?
          Jim Wahl

          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

           

          TopN_example.png

           

          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?
            Himanshu Modak

            Thanku so much Jim.