Sorting 100% Stacked Bar by category

gbuckley

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

GrantSmith

@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.

 

 

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.

Godzilla

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

GrantSmith

@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.

 

 

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.