partial forced sort to create legend order necessary

Erica_brackman

Hi, I am trying to build a stacked bar card in which the data in the bar is sorted in ascending/descending order but i want my 'Other' category to be at the top of the bar.

 

To do this, I created the forced sort beast mode calculation below and then tried to sort on an additional category after that.

 

Legend Order Calculation

(CASE when `PROD GROUP`='GROUP1' then 1
when `PROD GROUP`='GROUP2' then 1
when `PROD GROUP`='GROUP3' then 1
when `PROD GROUP`='GROUP4' then 1
else 2
end)

 

Sorting Order

Date Ascending (x axis variable)

Legend Order Ascending (calculation above)

Sum of Cost unaggregated (y axis variable)

 

It seems that after I sort in a dimension (i.e. vertically or horizontally) that I cannot further sort similar values in that same dimension.

For example, after i sort by legend order (which causes my 'other' group to appear at the top of the stacked bar), i cannot sort the remaining categories by sum of cost unaggregated.

 

Is there a way to do this or can this feature be added?

 

AS

Sorts should keep working after the first element.  One thing you might want to look at is the aggregation level on each sort.  Your first and last look aggregated but the second is not.  Throw an average function around your beast mode and see what happens.

Erica_brackman

To clarify, the first two sorts are working. It is the third one that fails. I tried averaging my beast mode calculation and that did not seem to help at all. My other category is still appearing on top, as before, but the remaining categories are not sorting according to the third sort category (since they all have the same beast mode calculation of 1).

AS

OK, so your third sort category is summed and sorted by des/asc?  Theoretically that should work if it's both aggregated and a sort direction is applied.

If it still doesn't work you could try putting both the second and third sort categories concatenated into the single beast mode so it's just one long string to sort by instead of two separate categories.

 

Something like 

concat(

case statement

,

'-'

,

sum(third category)

)

That will sort the second number as a part of a string, though, so you'd have to be careful there.

kshah008

@Erica_brackman, tagging you to check out AS's latest reply.