Custom Grouping or Binning

@cdutoit
,

In thinking about your challenge I was thinking about using a join recipe.

First you might create a 3 column table, something like this

Note you need to be careful not to have overlapping range values in this table.

Then you could use a join recipe to join the table above to the data you are showing in the first table.

There would be two conditions of your join conditions

revenue_aggregate >= min_val

revenue_aggregate <= max_val

If done carefully this can allow you to add a new categorical column dollar_range_name to each row of your data. Giving you the custom dollar values ranges shown at the top of the final table you are showing.

The same approach with a different table would allow you to add an employee count custom range as well.

What is not clear to me is the values in the cells of your final results. Is it something like an average across all of the organizations with similar employee count range and revenue range? If so this could be done by another group by recipe.

Just some thoughts that I hope are useful to you. Please let us know how you are getting on with this. Others please jump in with other ideas. There are of course many many ways to do this kind of thing.

P.S. You could also use a visual recipe and a formula step that uses some nested if statements to add a column with the custom range for revenue. And the same approach for an employee count custom range. Depending on the number of records you are dealing with and how often these ranges might change, the formula steps might be easier. Whatever you do the goal is to add two columns with these custom ranges. The next challenge will be sorting the ranges for presentation.