Pivot table sorting at subgroup/expanded dimension context level

[rafael_capote_qlik]

Hi Qlik Community,

I´m wondering if there is any good way to sort the pivot table at subgroup level?

For instance if you have a pivot table with 2 dimensions, sort the second one descending within each first dimension value.

The default behavior of Qlik sorting is:

Region	Country	Sales
Europe	Spain	1.000
	Germany	10.000
Iberia	Spain	20.000
	Portugal	5000

But the customer expectation is the following one:

Region	Country	Sales
Europe	Germany	10.000
	Spain	1.000
Iberia	Spain	20.000
	Portugal	5000

I´ve found some similar posts, but the solutions given are not OK for the users because the performance is slow, they want to rearrange the columns and they don´t want a straight table instead of the pivot table.

https://community.qlik.com/t5/New-to-Qlik-Sense/Pivot-Table-Sorting-Qlik-sense/m-p/1417392

https://community.qlik.com/t5/QlikView-App-Development/Sorting-on-the-pivot-table/td-p/783939

Do you know any way to achieve the expected result using either native objects or extensions?

I would really appreciate your suggestions here. Thanks in advance!

[agigliotti · Accepted solution]

Hi @rafael_capote_qlik ,

Did you try to enable the option "Sort by first measure" under sorting section?
I hope it can helps.
Best Regards
Andrea

[sakthi266]

Hi, 

   Add manual rowno in Country table as 1,2,3. and in pivot table sorting. Remove the option sorting numerically and sorting alphabetically. Just select sort by expression and in the expression give ORD. It helps.

COUNTRY:
LOAD * INLINE [
CID, COUNTRY ,ORD
1, GERMANY,1
2, SPAIN,3
3, PORTUGAL,2
];

[rafael_capote_qlik]

Hi Andrea,

Thanks so much for your reply. I tried before without success, but this time it worked perfectly for the specific example I posted. I should have been doing something wrong.

On the other hand, I´m still struggling with the real scenario I´m working on because I´m using some dimensions like Month Year that should be sorted in a specific way, so I need to mix somehow the First Measure for some dimensions and some other with Numerical and Alphabetical sorting orders.

I will try to find a way to handle different sorting orders, but I will accept your solution as it should be always the first option to sort Pivot Tables.

Again, Thank you.

[rafael_capote_qlik]

Hi Sakthi,

Thanks for your reply.

Your approach could work if the order is static, but if sometimes Spain has higher sales than Germany then we need a way to sort dynamically from Higher to lower sales.

@agigliotti answer will work in most of the scenarios

Best regards.

[rafael_capote_qlik]

I was checking deeper the Sort By First Measure option and there are some limitations that were causing errors when I tried it for first time:

• This option is only supported if subtotals are calculated. You can calculate subtotals by doing one of the following:
  • In the properties pane, go to Data, and click on a dimension. Toggle on Show totals.
  • In the properties pane, go to Appearance > Presentation. Toggle on Indent rows.
• This option is not supported for calculated dimensions.
• This option is only supported if all dimensions are in the Row section and all measures are in the Column section
• Pivot table help: https://help.qlik.com/en-US/sense/November2020/Subsystems/Hub/Content/Sense_Hub/Visualizations/Pivot...

Just take them into account if you are using this feature.