Sort and Order Stacked Bar Chart by Aggr and Rank

[asheppardwork]

I have seen several potential answers to my issue but none seem to get me 100% of the way.

In Qlik Sense, I have a stacked bar chart that is not sorting by the total of the stacked items.  It will sort by one of the dimensions or one of the measures but not by both.

I am using the VizLib Bar Chart.

Dimensions-

- Dept

-Trigger Year: 

=If( Aggr( rank (  count( {< RETURNED_STATUS = {'FALSE'} >} distinct RecordNum)  ),[Trigger_dt.autoCalendar.Year] ) >0, [Trigger_dt.autoCalendar.Year],$)

Measures-

-Count of Outstanding Returns:

=Sum( Aggr( count( {< RETURNED_STATUS = {'FALSE'} >} distinct RecordNum),[Trigger_dt.autoCalendar.Year], [Dept] ) )

Sorting-

-Trigger Year:

=rank (count( {< RETURNED_STATUS = {'FALSE'} >} distinct RecordNum) )

-Count of Outstanding Returns

-Dept

Any help you can provide to get these into the correct order would be much appreciated!

[Peter_Adams]

Based on your numbering, it seems you just want to sort (descending) by the :

 count( {< RETURNED_STATUS = {'FALSE'} >} Total <[Trigger_dt.autoCalendar.Year]> distinct RecordNum)

Multiply by -1 if Vizlib doesn't have an ascending/descending control.

[asheppardwork]

Thank you Peter for the tip.  After applying that in the sort I still have some departments that are out of order. Any Ideas?

[asheppardwork]

This looks like it is sorting in a descending way but only by the year 2023; instead of the total of the three years 2021-2023.  How can I get the full total of the three years to be sorted descending?

[vincent_ardiet_ · Accepted solution]

This should work if you just apply a custom sort on your first dimension "Dept" with a custom sort by expression below 
count( {< RETURNED_STATUS = {'FALSE'} >} distinct RecordNum)

[Peter_Adams]

I wonder if it is a Vizlib bug?  Can you replicate this in a Qlik chart?  And if yes, is the app small enough and the data not sensitive that you can share it?

[vincent_ardiet_]

IMHO this is not a Vizlib bug, this is just sorting 2023, not all series because the expression is not used to sort "Dept", look here when I'm sorting my first dimension:

[asheppardwork]

Thank you Vincent!  I don't know why I didn't see it before, but sorting by Dept then Trigger Year then Count with the Dept having your custom sort expression as descending, and trigger year left blank and count as numerically descending, did the trick!  I appreciate the help sir.