Hi, when working on a pivot table (not date type), the rows are listed by alphabetic order. Is there a way to list it in a different order. For example, if I have rows with “First, Second,Third,Fourth,Fifth”, I would like some how display the rows not by the first letter. For this particular one, I can work around with 1st,2nd,3rd,4th,5th, but wonder if there is a way to fix the order in similar situation (may be an order look up table or pairing or in my separate case, by the frequency/count of that particular grouping).
Thanks.
Hi Yan,
You can create one calculated filed which will have the order of row content and sort the pivot table based on that calculated field.
for example:
I have 3 rows of text
what’s up
Hai Jhon
how are you
I want display the above rows in the following order
Hai jhon
how are you
what’s up
to do so I have to create one calculated field say
order=ifelse(left(row,3)=‘Hai’,1,
left(row,3)=‘How’,2,3))
based on order column i can sort the row in pivot table.
I hope this will help you
1 Like
Hello @Yan-AWS - To add onto what @Shruthi already mentioned you need to create a calculated filed to create the custom sort key. Then you can add that (and hide that) in the visual and utilize the same to sort your visual. Hope this helps!
Thank, Shruthi. Will give it a try soon.
Thank you to you too, sagmukhe.
1 Like
Hi @Yan-AWS
Did Shruthi’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!
Sorry. Been busy with other things last few days. Thank you both for helping.
The solution is kind of work, but see if you can help improve. Let me use the initial “Sales Pipeline” dataset as an example. Using a Table visual, I have Salesperson as Group by and Weighted Revenue (sum) as the Value. The table default to alphabetic order for salesperson by the first name. Now I created a calculated variable “LastName” (and make the last name from split function). I can put LastName also in Group by. Now I can sort by LastName. However, I don’t want to show the LastName column after I ordered the table by it.
Similarly, in a bar chart, how do I order the x axis by the LastName, but showing the full name?
Hello @Yan-AWS - In Table visual, you can do that, by adding that Last name column, then configuring the sort by that and eventually hiding the column. Please see the below snapshot for your reference. Hope this helps!
In addition to that, you can take a look at the below video also to give you some idea. However, this method will only be possible when you have some finite set of values.
Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!
Hi Sagnukhe,
Thanks for the guidance.
The table part is working. So it is a solution.
The graph part is not working for some reason. It looks like should be a solution, but I can’t get it working. Again, use the sales pipeline dataset as example. After adding the LastName as the sort option, I am getting a strange order, neither a-z or z-a:
What could I do wrong?
Thanks