Analyzing "multi-select" values

Hi Niko,

It should be possible to do this, although there is no single direct solution. I would come down to the format of how the data are exported from your application.

Without further knowledge of your solution I can propose two ways of how to model this:

1: Duplicated lines for each user for each selection:

Then you can solve your case with following metrics:

First you count number of choices per user (named Choice by User):

SELECT COUNT(Choice) BY User

Then you count number of users with only one choice and with both choices:

SELECT COUNT(User) WHERE Choice by User=1 (named i.e 1Choice)

SELECT COUNT(User) WHERE Choice by User=2 (named i.e. 2Choice)

Then you can visualize this in pie chart by putting these two metrics.

To have percentages you can work further with these numbers, i.e.:

Percentage of 1 = 1Choice / (1Choice + 2Choice)

Of course your use case might be more complicated and you might have more than two choices, but that can be handled by filters directly in the metric:

I.e.: 

SELECT COUNT(Choice) BY User WHERE Choice IN (Panel 1,Panel 2) 

Also, with this approach, you might need to generate some unique ID to identify each line (or use compound key User + Choice).

2: Second way would be to have a separate attribute for every choice:

And the metrics would be similar - you would be creating separate metrics to count the users that selected the values.

Third approach would be to have all the selected choices in one string, i.e. “Panel 1,Panel 2” and use LIKE clause to extract the individual values, but I don’t think this is optimal, it would definitely require the most work at the end.

If would say the first option is more flexible - you don’t have to change the model if you would add a new field, but the metrics would be more intuitive in the second option. 

But as mentioned, it would come down to how you are able to extract the data.

I hope this helps.

Best regards,

Boris from GoodData Support team