Assign Your Data to Categories

Sometimes you need to group your data into categories that are specific to your organization and the analysis you need to do. This could be grouping dates into Holiday seasons, defining revenue ranges, or even defining your financial quarters. With the function BinRange, you can assign data into categories. Then, utilizing the Choose function, you can assign category names.

Let’s take a look at how this works using the 2010 Census Data. This is a Sample Data Set available to all Sigma users if you would like to try running this analysis yourself.

We’ll assign each of the 2010 County Populations into one of seven categories, name the categories, and then graph the count data.

First, we define our categories: Under 10k, 10k-20k, 20k-50k, Over 50k

This gives us the parameters we’ll use to build our categories, or bins, using the BinRange function. BinRange computes the bin for a value using the provided lower bounds. The function output is the bin number for the input value. When defining your bins using BinRange function, the first value entered is the input value. The values that follow define the lowest allowable value in the bins, which sets the lower bounds for the bins. If we enter binrange([input value], 100) we create two bins. Bin 1 will contain all numbers lower than 100, and Bin 2 will contain all numbers 100 and above.

When defining our function for our Population bins, we need to determine what our lower bounds for the categories are. Looking again at our population bins, we can see the lower category bounds are 0, 10000, 20000, and 50000. BinRange automatically assigns to Bin 1 anything lower than first defined lower boundary, which means we do not need to include 0 as a lower bound. This gives us the following function: BinRange([Population 2010], 10000, 20000, 50000)

This will give us 4 bins, and each population value will be assigned to one of the 4 bins. Bin 1 is <10k, Bin 2 is 10k-20k, Bin 3 is 20k-50k, and Bin 4 is >50K. As you can see below, Washington County with a population of 17,581 is assigned Bin 2.

We can now use the function Choose function to name our bins.

Choose allows you to input an index number and a list of values, then returns that value corresponding to the entered index. This is a very useful function when paired with BinRange because you can easily name your bins. In this case, the index numbers we enter into the Choose function will be the bin numbers returned by BinRange. The values will be the names of the different bins. Bin 1 is named “<10k”, Bin 2 is named “10k-20k” and so on.

Choose([2010 Pop Size Bins], “<10k”, “10k-20k”, “20k-50k”, “>50k”)

This makes it much easier to keep your different bins straight. Once you have the bins named, you can then create a bar graph of the counts for each bin, and order the x axis by bin number.

You can also Group By State and then Group By your bins. Create an aggregate column to show the number of counties in each bin by state. You can visualize this data utilizing a stacked bar chart.

See the power of cloud BI in action.  Schedule a demo today.