Sorting 100% Stacked Bar by category

Reply
Orange Belt

Sorting 100% Stacked Bar by category

I have a 100% Stacked Bar Chart for net hours worked. Each bar is an employee and is broken into net hours by pay category (OT1.5 vs. OT2.0 vs. Reg).

 

I want to sort the chart so that the employee with the lowest percentage of OT1.5 is on top and the employee with the largest percentage is on bottom.

 

I started by fiddling with the "Sort on Totals" toggle under "General" in the Chart Properties pane. However, this sorts the bars by total net hours worked in all categories, not by percentage in one category.

 

So I made a beast mode (see attached photo). I checked to make sure that the beast mode calculates correctly - it does (see attached photo of single bar chart with "Sort Order" as the values field. It correctly returns the percentage of OT1.5 hours for each employee). But when I use the "Sort Order" beast mode to sort the 100% stacked bar, it does not work (see photo of 100% stacked bar with "Sort Order" in the sorting pane. The bars are not sorted correctly). It looks like the issue is that when I use the beast mode to sort, Domo calculates the beast mode for each row of the dataset instead of as an aggregate (see photo of data table. "Sort Order" returns 1 for OT1.5 rows and 0 otherwise).

 

Any pointers on how to adjust the beast mode so that the bars sort correctly is much appreciated!        

 

*All data is fictional and used only to illustrate this idea


Accepted Solutions
Highlighted
Blue Belt

Re: Sorting 100% Stacked Bar by category

@rgbuckley 

 

Because how the 100% stacked bar works and expects a single column for a series it's not quite possible. However! You can hack around it with a normal stacked bar and then plot the percentages.

 

Screen Shot 2020-03-12 at 3.52.26 PM.png

 

You'll need to make distinct beast modes for each type of Pay Cat Name using the same formula you posted earlier, just changing the value you're comparing against.

SUM(
  CASE
  	WHEN `Pay Cat Name` = 'OT1.5'
  	THEN `Net Hours`
  	ELSE 0
  END
)
/
SUM(`Net Hours`)

Reg, OT1.5 and OT2 etc.

 

You can then sort based on the OT1.5 beast mode ascending which will bubble the records with the lowest OT1.5 percentage at the top.

 

One caveat to this approach is that you do lose the total amount of hours per type.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Highlighted
Major Brown Belt

Re: Sorting 100% Stacked Bar by category

this is a tough one. Interested to see if anyone else has a solution to this. 

Domo Arigato!

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'
Highlighted
Blue Belt

Re: Sorting 100% Stacked Bar by category

@rgbuckley 

 

Because how the 100% stacked bar works and expects a single column for a series it's not quite possible. However! You can hack around it with a normal stacked bar and then plot the percentages.

 

Screen Shot 2020-03-12 at 3.52.26 PM.png

 

You'll need to make distinct beast modes for each type of Pay Cat Name using the same formula you posted earlier, just changing the value you're comparing against.

SUM(
  CASE
  	WHEN `Pay Cat Name` = 'OT1.5'
  	THEN `Net Hours`
  	ELSE 0
  END
)
/
SUM(`Net Hours`)

Reg, OT1.5 and OT2 etc.

 

You can then sort based on the OT1.5 beast mode ascending which will bubble the records with the lowest OT1.5 percentage at the top.

 

One caveat to this approach is that you do lose the total amount of hours per type.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Announcements
Want to join our weekly online meet up? Please visit this board here and let me know. Click here for more details!