I use a service that uses Quicksight to analyze data. Part of the selected data is gathered as a multiselect list and stored as a comma separated string.
for example it would be like
Data set 1: value1, value2, value3
Data set 2: value1, value3
Data set3: value 1
when I try to display the count of each value using a vertical bar chart.
it shows the whole string as one value, so the count would be as follow
value1, value2, value3 (1)
value1, value3 (2)
value 1 (1)
What I want to show is
Value 1 (3)
Value 2 (1)
Value 3 (2)
I tried using a split function, but it would only store one of the three possible values in the string.
Any suggestions on how to do it?
Hello @bakerba3200 , welcome to the Quicksight community!
To do this in Quicksight, I think the best option is to use the split function. You would need to create a function for each value position. I would recommend doing this on the dataset layer rather than the analysis.
The option that might be easier is doing this in SQL prior to ingesting into Quicksight. So you could either do this to the dataset from its source or using Custom SQL (Using SQL to customize data - Amazon QuickSight) to query your source from Quicksight.