Pivot table sorting of multiple dimensions with dual is not working in Qliksense

[sunainapawar]

Hello All,

I have a scenario where i have multiple dimensions and multiple measures in a pivot table.

Each measure must be sorted by Sum(total). I have done that this in Sorting tab by directly giving Sum(total) expr. It works properly for 1st and 2nd Dimension., but for 3rd dimension,values are not sorted properly. Hence for 3rd dimension i used Dual as below.

=dual(Second,aggr(sum(Total),Name,First,Second))

By this expr values are sorted properly but in front end when we select values for 3rd dimension, it automatically selects values from First and Second dimension., Not sure why it is considering all above columns. Kindly assist.

values are sorted properly. 

When selection is made on Second dimension it appears as below.

[rubenmarin]

Hi, values are created using First and Second, so they are selected, I'm not sure if ther is a workaround to avoid that.

=dual(Second,aggr(sum(Total),Name,First,Second)) -> it created a Second value for each Name, First and Second, so the 3 fields are linked to each value.

[sunainapawar]

Thanks Rubernmarin for the response.

Yes I could not find any workaround for this. Do we have any other way to achieve this sorting. 

[rubenmarin]

Hi, I suppose that you are doing this because Second field can have the same values in diffreent segments, and it could be sorted first in one Name-First combination and last in another Name-First combination.

In that case, no, I don't know any other way, you need to make it a different value by each Name-First combination, an to make that each value is linked to Name and Fisrt values.

You can use a filterr outside the table to make selections only on that field.

[sunainapawar]

Thanks for the response rubenmarin. Yes values will get repeated in different segments.

I also tried to create a key in the script, like Name&First as Key, Name&First&Second as key1. in the front end in my Second dimension expr i used as 

=dual(Second,aggr(sum(Total),key1)).

This sorts properly and even selection works but issue is it only list one segment and ignores others. My Second value is repeated but it is ignoring that.

[rubenmarin]

Hi, that's a good try but I think it will also make selections on key1 field, wich also will reduce the values of Name and First associated with this key.

BTW I usually use RowNo(TOTAL) to ensure each combination has a unique value, so:

Dual(Second,aggr(RowNo(TOTAL),key1)).

It's just an improvement but I don't think it changes the current behaviour.

Using an external filter (set the field outside the table) can't be a solution? Not ideal because it will be better making selections directly in table but at least you have a way to make selections on only [Second] field.

[sunainapawar]

Hello,

Thats true. Using External Filters is an option but user doesn't agree for that.

Will try using Rownno(Total). Thanks for the suggestion.

[sunainapawar]

Hello All,

Do we have any other way to achieve this sorting in pivot table?

I tried using variable input object ,defined a variable and tried sorting it by Dynamic Values. But in dynamic values, we are not able to use set analysis hence that is also not working. Only direct Sum(Total) works in dynamic values.

Please suggest, if we can solve this by any other way. Appreciate a response.

Thanks in advance.

[rubenmarin]

Hi, just to check it you have already tested the option 'sort by first measure' on the Sort tab, this can also sort each value in different position by segment, but it only works when sorting by the first measure.