Counting the frequency of unique elements from a group of lists

[andrekeuck]

Hi!

I’m trying to display a chart with a dynamically generated count of Things from the database. Reading through threads here, it looks like Group By might be the answer, but I can’t use it because I’m trying to count off a List field, and so would have to refactor my database table. Before I do that, I was hoping there was a simpler answer…

I have a Thing “company”. Each “company” has a column (the list) called “ownership”, which can have N designations included in it, such that:

Company

• Woman owned
• Minority owned
• Veteran owned

Any company could be zero or up to all three of those, such that the list field “ownership” could be empty or potentially read “woman owned, minority owned, veteran owned”.

For any selection of companies, I want to present a graph that shows the number of companies that meets each unique designation.

So for some subset of companies, I’d want the graph to show (for example):

• Woman owned companies: 3
• Minority owned companies: 2
• Veteran owned companies: 6

Understanding that one company may carry zero, one, two, or three of those designations, and could therefore be counted up to three times, once per designation.

I’ve been able to do this in a repeating group, where the first column is the ownership designations, then I use the Column 1 value to do the count in the second column, but I can’t figure out how to consolidate this into a single search to provide the graph with a list of numbers to plot.

Am I in refactor territory, or have I missed a simple resolution?

[hergin]

I think you can use all the possible owned by options as X-axis and then count the Y axis values of the chart by using do a search for.

I can display the chart like this:

For this data:

And here are more details:
I have this structure for a company:

And this option set for owned by (it can be a data type as well, it doesn’t matter):

And the properties for this chart are as follows:

[andrekeuck]

That works perfectly in the Bubble chart plug in; thank you!

I’ve been trying to make it work in the Air Chart Lite plug-in, without success. It doesn’t seem to include or grant access to the “Current point” option you used for “Owned by contains”…

[hergin]

Yes, with AirChart plugin, it is not as straightforward and you have to do some tricks, but still possible:

See the category (X-axis) and series1-data (Y-axis) below:

For the series 1 data:
All Owned By options:

Formatted as text:

Each item in the text is a Search For Companies with the current Option (This Owned By):

Then, the rest is obvious.

[andrekeuck]

Brilliant. This is it exactly. Thank you so much, again, for your help.