How can I sort a table by two columns in sisense?

Question

I use Sisense Version: 20.21.6.10054 on Windows.

I need to sort a table widget in sisense by two columns, first by name, and second by number of behavior that person demonstrates.

The result should look like this:

id   first_name   last_name   behavior_NO  behavior_link
1      Ben          Smith        1           behavior_1
1      Ben          Smith        2           behavior_2
1      Ben          Smith        3           behavior_3
2      Sam          Johns        1           behavior_1
2      Sam          Johns        2           behavior_2
3      Martha       Star         1           behavior_1
3      Martha       Star         2           behavior_2
3      Martha       Star         3           behavior_3
3      Martha       Star         4           behavior_4

Now, when I sort by Last_name the behavior_No is not sorted in correct order, but it looks like this:

id   first_name   last_name   behavior_NO   behavior_link
1      Ben          Smith        1            behavior_1
1      Ben          Smith        3            behavior_3
1      Ben          Smith        2            behavior_2
2      Sam          Johns        2            behavior_2
2      Sam          Johns        1            behavior_1
3      Martha       Star         4            behavior_4
3      Martha       Star         2            behavior_2
3      Martha       Star         1            behavior_1
3      Martha       Star         3            behavior_3

Sisense does not allow to sort by two columns in a table.

I tried to pivot the table but the problem is that there is a column with hyperlinks in it, and when making a pivot hyperlinks display like a text (<a href="https://https://stackoverflow.com/ ) but not like a link.

Can anyone advise on how to solve this, either to sort the table by two columns or to insert a hyperlink in a pivot?

Thanks in advance.

Answer 1

Maybe you already find a better way that the following but yesterday I had a requested to do a rank but also, ordering three columns. First I needed order by the Target then by Rank and then by Sales so in the pivot table can look like this:

Sales_Person | Target | Sales | Rank
   Joe       |  100%  |  12   |  1
   Chris     |  100%  |  12   |  1
   Maria     |  98%   |  11   |  2
   Peter     |  97%   |  10   |  3

So because Sisense in the front end does not allow to sort two or more columns there is a built-in function called "ORDERING".

In the following link you will find the function under "Other Functions"

Function References Sisense

The only disadvantage is that at the time you implement this function it will create an additional column for ordering so at the end I obtained the following results:

Sales_Person | Target | Sales | Rank | Ordering
   Joe       |  100%  |  12   |  1   |    0
   Chris     |  100%  |  12   |  1   |    1
   Maria     |  98%   |  11   |  2   |    2
   Peter     |  97%   |  10   |  3   |    3

Also, keep in mind that all the different columns should be dimensions.

By the way, the version I have is Sisense L2022.4.0.222

Answer 2

Just create the sort ordering column on the database side. Assuming the database is SQL based something like this would work.

, ROW_NUMBER() OVER (ORDER BY first_name, last_name, behavior_NO) AS SortOrder

Put whatever columns you want to sort by after the ORDER BY and before the close parenthesis.

Then add that into the data model via a custom query.

This is extremely similar to https://stackoverflow.com/a/73675094/3315862 but one layer back.