Stacked Bar Chart Sorting Incorrectly

@ckatzman, I like this soltion and it works.

Ideally, I think you would agree that this would be built into the functionality of a stacked bar, correct?

If so, I'd appreciate it if you would upvote the idea that I just created for it: https://dojo.domo.com/t5/Ideas-Exchange-suggest-and-vote/Ability-to-Sort-Stacked-Bars-by-the-Category-Total/idi-p/12534#M2268

Thank you,

Nick

@theCliffs, sure. Let's say you have a field called 'Signal' that results in 3 potential values, 'Green', 'Yellow', and 'Red'. In order to better control the stack order and structure of a stacked bar, rather than just bringing in 'Signal' as your 'Series', you need to create the fields separately as individual Beast Mode calcs.

So, in Beast Mode, you would do something like this... SUM(CASE WHEN `Signal` = 'Green' THEN 1 ELSE 0 END), and repeat that for each of your potential values, and you name it `Signal_Green`. Now, instead of applying `Signal` as your Series, you instead bring in `Signal_Green`, `Signal_Yellow`, and `Signal_Red` (you can add more than one "series" item).

Then, in Sort Order, you would want to first Sort on SUM or COUNT of whatever your defining metric is (I used 1/0 in the example above, but you may have some field you want to use like `Sales` or `Revenue` that you are actually trying to sum). That will define your overall stack order from tallest to shortest (or vis versa). Then you bring in each of your Signal_Green/Yellow/Red fields into the Sort Order in which ever order you want them to appear in the stack. See example...

All comments

Nick,

We're looking into this and will let you know as soon as we have an answer for you.

It looks like it might be an incorrect approach. You're wanting to sort on the x-axis, but your sort is using the dimension on the y-axis. I wouldn't expect that to work. Based on your screenshot, you're using a Beast Mode to create your product grouping. So try this:

Copy everything that's in your Product Grouping Beast Mode into a new beast mode. Then do a SUM(`SUM of Open Pipeline`) and concatenate it with the Product Grouping. The result should look something like this:

2345 Product Group 1

12345 Product Group 2
345 Product Group 3

Use the new Beast Mode for the sorting (descending). Please give that a try and let me know the result.

Thank you for taking the time. This solution is not working for me. "SUM of Open pipeline" is not a field. on my data set ("Open Pipeline" is a field, and SUM is the summary type that I am choosing within the card builder).

This solution will not work because of that.

Theoretically, I could do a data-level transformation to get the total open pipeline by proiduct grouping, but that is not feasible because it would take away functionality if the user were to change the time period.

Thank you,
Nick

Gotcha. Change it to SUM(`Open Pipeline`) and let me know the result.

Here is my beastmode formula:

CONCAT(

SUM(`Open Pipeline`),

CASE WHEN `Product Grouping` IN('Mobilisafe','ControlsInsight','Other') THEN 'Other'
WHEN `Product Grouping` = 'UserInsight' THEN 'IDR/UBA/AR'
ELSE `Product Grouping` END
)

This doesn't see to solve the issue

beastmode error 3.PNG

Thank you,

Nick

Hmm . . . what happens if you remove the series? Does it work then? If not, I would try looking at the results of the beast mode by itself in a table card to make sure it looks like you'd expect to see it.

@RabidCougar

- If I remove the series from the sort, then it sorts by default.

- If I remove the series from the "series" field, then I no longer have a stacked bar chart

- If I view my formula in the table view I get what I would expect to see: the open pipeline (of that particular row of data) concatenated with the product grouping

Also:

- If I am using a concat, then by default this will be a text, correct? Therefore it would sort not based on value, but based on ordering the information alphabetically by text (meaning, for instance that 1200 would come before 300 which would come before 5000, which would come before 90) Correct? I believe this is partially why the proposed solution is not working

- You mentioned that you wouldn't expect it to sort properly because I am sorting on my value from the Y-Axis. however, if it were a single bar and I sorted based on the value in the y-axis then it would sort properly. I understand why a stacked bar would be different, but I'm not sure that I follow your logic.

Thanks again for your support,

Nick

You would be getting a text result, yes--and that's where it's blowing up. Hmm . . .

If this will work, I think I might have a way around that. Try this:

Let's convert the number to a date, which will then sort the same whether it's a date or a string.

FROM_UNIXTIME(SUM(`Open Pipeline`))

As to your question about my logic, it's because I think that with the stacking, it's breaking SUM(`Open Pipeline`) down into sum by Pipeline Aging Bucket and Product Grouping, which isn't the right number for sorting. Make sense?

I've always encountered this same problem of wanting to stack tallest to shortest first, and then secondarily by a "series" order, but could never get it to work with just using a single "series" value. I worked around this by creating a separate beast mode result for each possible series value, and then bringing each of those values into the "stack" as a separate item and adding them in presentation order to the Sort box.

Just curious, nlombardini, have you had a chance to try out my proposed solution yet? If so, I'm curious to see if it does the trick or not.

Hi @RabidCougar,

Unfortunately converting the sum to a date type did not solve the issue.

Would you agree that it appears like there should be the ability to sort stacked bars based on the total in an easy way? Don't get me wrong, I strongly appreciate the creative attempts at a solution, but it shouldn't be this hard, right?

Thank you,

Nick

@ckatzman, I like this soltion and it works.

Ideally, I think you would agree that this would be built into the functionality of a stacked bar, correct?

Thank you,

Nick

I agree wholeheartedly that this would be something we should be able to do. So without hesitation I upvoted your Idea. Since there is a series in a stacked bar chart graph, which makes it create a sum based on the combination of the series and x-axis parameter, I suspect that to get the sum of the stacked bar, it would involve making the sum of that sum, which isn't something SQL allows you to do. So we may be fighting a SQL language limitation here. We'll see. I have my fingers crossed that we aren't.

You've got it working, which makes me happy. Would you consider posting the Beast Mode(s) involved, as it would be helpful to anyone else in this same boat who stumbles across this thread in their search for a workaround?

Thanks!

theCliffs

@ckatzman, can you point me to (or post) how you created separate beast mode results for each series value and then brought them into the stack as separate items?

[Deleted User]

@theCliffs was @ckatzman's reply helpful for you?

Thanks!

Dani

theCliffs

@DaniBoy Sure was. That's why I liked it.

Thanks!

jlafee

Thank you @ckatzman for this solution. Just ran across this scenario for the first time today trying to sort a stacked bar and this was a life saver.