I have a stacked bar chart that is filtered by a dropdown field which allows me to flip between different visuals. Each visual I am changing between may have different values (and therefore different legends). Sometimes it is numeric (i.e. 1-10) and sometimes it is text description (however, I don’t just want to use alpha sort.) I would like to be able to set this stacked bar visual to pull its sorting for the legend and the chart display from a specific field which will list out all the appropriate options for that question in the correct order. Is there a way to assign the sort based on a list of values in a field?
Here is an image of the bad sorting I would like to fix.
Hello @kakervall, I believe sorting the visual in the field well for Group/Color should get you the result you want. You can select ascending or descending for a numeric value or sort a-z/z-a for a string value. This will also update the display of the legend for that visual. Let me know if that helps!
Hi @DylanM! Thanks for the message. The thing is that I want to be able to sort based on something other than numeric order or alpha order. I want to sort based on a predetermined list that is stored in another field. For instance, if I sort based on alpha and my possible values are: Starbucks Light Roast, Starbucks Medium Roast, Starbucks Dark Roast; the result will be Starbucks Dark Roast, Starbucks Light Roast, and Starbucks Medium Roast… not what I want. Or, for instance if the possible values are: Yes, Maybe, No; it will alpha sort to Maybe, No, Yes. HOWEVER, I have a field with the possible values listed in the order I want it to appear. In these examples this field holds {‘Starbucks Light Roast’, ‘Starbucks Medium Roast’, ‘Starbucks Dark Roast’} and {‘Yes’, ‘Maybe’, ‘No’}. I want the sort order to be determined by whatever order the values appear in the list in this field. Any ideas?
Hello @kakervall, this would be a kind of annoying solution but I believe it would work. If you want to control the order you could use a calculated field with an ifelse statement. Something like this:
ifelse({SurveyResponseValues} = "Starbucks Dark Roast", "Starbucks Dark Roast",
{SurveyResponseValues} = "Starbucks Medium Roast", "Starbucks Medium Roast",...etc)
I believe if you used that as your field instead, and you order them how you want from first to last in the ifelse, the display will mirror that exactly. Let me know if that helps!
Thank you @DylanM! I’m not sure I quite follow, but I’m interested!.. My calculated field would be based on the SurveyResponseValues field and would just list out all the possible values in the order I want them? Is that right?
I actually have another field that contains all the possible values in the order I want already baked into my dataset. It is the field with my “rating scales.” And example value from this field looks like: {‘1’,‘2’,‘3’,‘4’,‘5’,‘6’,‘7’,‘8’,‘9’,‘10’} or {‘Starbucks Light Roast’,‘Starbucks Medium Roast’,‘Starbucks Dark Roast’}. I guess it has the formatting { and ’ ’ bits… should I strip that out before bringing the field into Quicksight?
Where would I put this field exactly in the field wells for the visual? Thanks for your help and suggestions!
Yes, @kakervall, your initial statement is what I was thinking. It would check you actual field for a specific value and you would list them in the order you would want to display them. You would set the calculated field into the group/color field well instead of the SurveyResponseValues field.
As for that other field that you have available, I do think it would simplify the process if you edited the formatting before pulling it into QuickSight or altered the formatting within your SQL query that builds your dataset.
