Group value as bins and rename the bins

I know how to group the values in a column, which is to use Bin function, but after that, how can I rename the bins from 1,2,3,4,5 to meaningful values?

Use Choose function to rename the bins.
Case study: you have a column which is about Product ID from 1-3000. And you would like to create 3 groups to see the value distribution. The groups are ID 1-1000 as Made 2010, 1001-2000 as Made 2011 and 2001-3000 as Made 2012.

Here are the steps:
1. Create bins: BinRange([Product ID], 1000, 2000, 3000). Then you will get a column bin_field and values show as 1,2,3. Note: Product ID needs to be a number column with β€˜123’ icon.

2. Replace the bin values with text:
Choose([bin_field], β€œMade 2010”, β€œMade 2011”, β€œMade 2012”). Then the value 1,2,3 will be replaced with Made 2010, Made 2011, Made 2012.

Fanfan, when you use a function like BinFixed([Sales], 0, 1000, 10), you are basically telling Sigma to break up any sales between 0 and $1000 into 10 equal bins. Each bin is identified by its sequential number, 1 through 10, with any outlier getting into the bin # 11.

If you want to name the bins in a self-explanatory way, you have 2 options:

  1. Encase your bin statement into an IF:
    if( [SalesBin] = 1, β€œ0-100”,[SalesBin] = 2, β€œ101-200”,[SalesBin] = 3, β€œ201-300”, β€œOTHER”)

  2. Don’t use bins to start with, and use the IF instead:

if([Sales] <= 100, β€œ0-100”, [Sales] <= 200, β€œ101-200”, [Sales] <= 300, β€œ201-300”,β€œOTHER”)

Thanks for the details, Yuri~ I think I used BinRange instead of BinFixed? With BinRange, it will set the bins based on the bracket I defined?

Woo, good to know that Case function is supported in Sigma! But I did not see it show up in Formula dialog :thinking: image

@Fanfan , you are right, CASE WHEN works when you write SQL, its Sigma function equivalent is if() .

Our if() supports multiple sections / choices and would look like this:

if( [SalesBin] = 1, β€œ0-100”,[SalesBin] = 2, β€œ101-200”,[SalesBin] = 3, β€œ201-300”, β€œOTHER”)

I corrected my initial reply above.

Thanks

1 Like