Using the Multi-field Binning within different groups?

[TRW]

Hi all,

Long-time listener, first-time caller here.  I'm trying to figure out a way to use the Multi-field binning tool but have each set of bins within a certain group.  I am trying to build a reusable, automatable workflow where the input is a list with hundreds of accounts (a varying number each time I run the workflow), and thousands of users (also varying each time) and their usage (also varying), and rank each user, within the context of their firm, as a high, medium, or low user based on their activity.

Here's an example of the data, sorted by firm and user alphabetically:

Firm	User	Usage
Acme	Bill	89
Acme	Cathy	103
Acme	Eddie	101
Acme	Gail	72
Acme	Liam	59
Acme	Monty	37
GeneriCo	Blythe	14
GeneriCo	Cooper	26
GeneriCo	Fred	17
GeneriCo	Harry	10
GeneriCo	Linda	501
GeneriCo	Wilma	3

When I apply the Multi-field Binning, with 3 equal records, here are the results:

Firm	User	Usage	Usage Category I get using Multi-Field Binning, 3 equal records
GeneriCo	Linda	501	1 - High
Acme	Cathy	103	1 - High
Acme	Eddie	101	1 - High
Acme	Bill	89	1 - High
Acme	Gail	72	2 - Med
Acme	Liam	59	2 - Med
Acme	Monty	37	2 - Med
GeneriCo	Cooper	26	2 - Med
GeneriCo	Fred	17	3 - Low
GeneriCo	Blythe	14	3 - Low
GeneriCo	Harry	10	3 - Low
GeneriCo	Wilma	3	3 - Low

Basically, the tool is ranking all the usage, and then sorting in to three groups overall.  Here's what I need, where the ranking and sorting takes place within each firm:

Firm	User	Usage	Usage Category I need - binned within each firm
Acme	Cathy	103	1 - High
Acme	Eddie	101	1 - High
Acme	Bill	89	2 - Med
Acme	Gail	72	2 - Med
Acme	Liam	59	3 - Low
Acme	Monty	37	3 - Low
GeneriCo	Linda	501	1 - High
GeneriCo	Cooper	26	1 - High
GeneriCo	Fred	17	2 - Med
GeneriCo	Blythe	14	2 - Med
GeneriCo	Harry	10	3 - Low
GeneriCo	Wilma	3	3 - Low

The change can best be seen with GeneriCo employees.  In the first sort, Cooper is ranked in the Medium group, but in the second sort, he's ranked in High.  Fred and Bylthe both are first ranked Low, but in the context of GeneriCo, they are Medium users.

I can't figure out how to do this.  Does anyone have a suggestion?  Thanks in advance, all you clever people.

-Rich

[Thableaus · Accepted solution]

Hi @TRW

I'd use the Tile Tool, have you tried that? With the Tile Tool, you can group by a determined Field (in this case, Firm)

You could even use the Smart Tile option to get a better result (I don't know if it fits what you're trying to achieve).

WF appended.

Cheers,

[TRW · Accepted solution]

Hi Thableaus,

You are one of the clever people!  Thank you.  Your solution worked.  The only change I made was to add a Sort (by Firm, and then by Usage, descending) before the Equal Records tile.  I was getting some incorrect results due to the random order of my actual input.

The Smart Tile would work for someone who wants usage automatically categorized into buckets based on usage.  In my case, I need three equal-sized segments.

I've updated the solution and added it.

Your help much appreciated!