Sorting by multiple columns

Question & Answer
,
1

Hi,

Is there a way in quciksight to sort the table based on multiple columns?
like order by a_column, b_column, c_column in sql

1 Like
2

Hi Dejana,

This should be possible through the use of a calculated field. Can you test out the below calculated field and then sort by it in your visual? You can swap out the type of aggregation to what makes sense for each column and of course pick whether you want them to be ranked in ascending or descending order.

rank([sum({a_column}) DESC])*10000 + rank([count({b_column}) DESC])*1000 + rank([count({c_column}) DESC])

If you follow the above format, your visual will be sorted in the following order: a_column, b_column, c_column.

1 Like
3

Is this the same for dimensions and measures?
I want to sort the table visual by two types – a status field which is yes/no and total spend field which is numeric.

4

Yes it should work the same. Depending on how you want it sorted you could say rank([avg({yes_no}) DESC])

5

Thank you for your response Max. Looks like sum(), count(), avg() do not work with string values. any other options to try?

Expression rank([avg({account_status}) ASC])*10000 + rank([sum({monthly_spend}) DESC])*1000 for function AVG has incorrect argument type AVG(String). Function syntax expects ‘AVG(Number)’.

6

Ahhh yes. Can you cast with an ifelse statement to an integer?

ifelse({field}=‘yes’,1,0)

1 Like