I have a database with several data types:
Question - contains question text and list of response options
Response Option - contains response option text
User Answer - contains a list of response options (those that the user selected) and a question that the answer is linked to.
I want to collate the answers, and for each response option display the count of answers that contain it divided by the count of answers that contain the most popular (i.e. included in the most user answers) response option. I have been trying to figure this out using various logic rabbit-holes for the past two hours but made no progress.
does anyone have a solution to this please?
Thank you!
To find the most popular response option, do a search for responses, grouped by response option, with an aggregation of ‘count’, sorted by count, first item’s response option.
To get the count for each option, do a search for responses, grouped by response option with an aggregation of count. Then you can divide each grouping’s count by the previous count (i.e. the count of the most popular response option).
1 Like
But how can they be grouped by response option when each has a list of response options?
Ah.. I see… I missed that.
You have multiple response options on each Response.
Well, you can’t use Groupings for lists.
There are probably 2 solutions.
Solution 1 (the more ‘Bubble’ option, and probably the most performant)
Create a new Datatype of response_ResponseOption to act as a ‘joining’ table - which links a specific response to a specific response option.
So if 3 options are selected for a response, create 3 response_ResponseOptions, one for each, connected of the selected responses.
Then you can use a search for response_ResponseOptions, grouped by Response Option.
Option 2 (requires no changes to the current database structure, but will not be scalable, so unsuitable for large datasets)
Use javascript to establish the most popular option, and the aggregated data for each response.
This will obviously involve loading all of the responses (whether on the sever or the browser), so will not be suitable for very large datasets.
1 Like
Thanks Adam! I am tempted to put this in the too hard basket for now because its not core to how my app works and there is a lot else on my to do list. However, it’s worth sharing exactly how this problem manifests because there may be a different way of presenting this that circumvents the list of lists of things problem.
below is a screenshot of a graph in my app. I want the blue lines’ length to be relative to the highest scoring line (at 100%) rather than currently relative to the % of answers that included that. So the answer that is 53% would be fully blue, and the one that says 26% would be around half blue etc. I currently control the lines using html custom css widget to set the max width dynamically. This results in a relatively nice presentation without having to bother with setting up graphs and things. But is there a better way to do this?
I made a quick demonstration of how you can achieve this in bubble with a plugin. In the video I used my Plugin Data Jedi, but I suppose there are others that could be used to achieve the same result.
With this approach you do not need to alter your data structure and incur extra WU costs for that, nor do you have a restriction on the size of the dataset as you do not need to load the entire dataset, unless you want to and can instead just use some constraints on the search to only return the entries you want.
