4.3.6 Creating a Numeric Range Column

A numeric range column groups numeric data into a predefined set of bins or ranges, for example:

Less than 5

5 to 10

10 to 15

15 to 20

Greater than 20

You can create a range column for any numeric column in a Data Table. To create a range column, follow these steps:

1. Right-click the header of the column for which you want to create a range column, and select the 'New Range Column' option from the context menu. This opens the 'Value Range Name' dialog box

2. Enter a name for the new range column, and click 'OK'. This opens the 'Numeric Range Option' dialog box. Here you can specify the different benchmarks defining the range.

To customize a bench­mark label, double-click the benchmark value. to enable edit­ing.

3. Enter a benchmark value into the 'Value Range' field, and click the 'Add' button. Repeat until all desired benchmarks have been entered.

4. Select the 'Less Than Min' checkbox to create a bin for all values that fall below the minimum benchmark. If you do not select this option, those values are classified as “Others.”

5. Select the 'Greater Than Max' checkbox to create a bin for all values that fall above the maximum benchmark. If you do not select this option, those values are classified as “Others.”

6. Click the 'OK' button to close the dialog box.

See Also

Creating a Named Grouping, for another way of partitioning column data.

Creating a Date Range Column for partitioning date information.

Example: Creating a Numeric Range Column  >>

Consider the 'Sales by Category' query, which lists the total amount generated from the sales of different categories of products. You can define a range column for the 'Total Sales' field, which places each amount into a predefined range (bin).

1. Create a new Worksheet and drag the 'Sales by Category' query into a vacant cell.

2. Right click the 'Total Sales' column, and select the 'New Range Column' option. This opens the 'Value Range Name' dialog box.

3. Enter the name “Revenue Range” and click 'OK'. This creates the new column and opens the 'Numeric Range Option' dialog box. Here you can specify the different benchmarks in your range.

4. Deselect the 'Less Than Min' and 'Greater Than Max' options.

5. Enter the first benchmark value, 750000, into the 'Value Range' field, and click the 'Add' button.

6. Enter the next three benchmarks in turn, clicking 'Add' after each one: 1000000, 4000000, 10000000.

 

This creates the three ranges: (1) 750,000-1,000,000, (2) 1,000,000-4,000,000, and (3) 4,000,000-10,000,000.

7. Double-click the '750000-1000000' label and enter the new label “Low”.

 

8. Repeat the above step to relabel the 1,000,000-4,000,000 range as “Medium” and the 4,000,000-10,000,000 range as “High”.

9. Click the 'OK' button to close the dialog box.

10. Preview the table data. Note that each cell in the 'Total Sales' column has a corresponding range in the 'Revenue Range' column. The values that lie outside the specified range are labeled 'Others'.

 

11. (Optional) Instead of defining a fixed minimum and maximum value (750,000-10,000,000), you can keep the numeric range open ended. Follow the steps below:

a. Open the 'Numeric Range Option' dialog box by clicking the icon in the column header (see Editing a Range Column).

 

b. In the dialog box, select the 'Less Than Min' option, and click the 'OK' button.

 

c. Preview the Data Table and note that the values outside the specified range (formerly labelled 'Others') are now labelled '<750000.00'.

 

<< 4.3.5 Automatically Substituting JavaScript For SQL © 1996-2013 InetSoft Technology Corporation (v11.5) 4.3.7 Creating a Date Range Column >>