Stacked Bar Chart Sorting Incorrectly

nlombardini
nlombardini Contributor
in Charting

Hi,


Let's say I create a stacked bar to show "Open Pipeline by Product," with a series that tells me how old those opportunities are.   I would like to sort this based on the value total of the stacked bar (ie highest of the left, lowest on the right).  However, when I attempt to do so, It never seems to work properly.  Is this a bug, or am I doing something incorrectly?

 

Sorting Issue_DOmo_050316.png
Thank you,

Nick

Best Answers

  • nlombardini
    nlombardini Contributor
    Answer ✓

    @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

  • ckatzman
    ckatzman Contributor
    Answer ✓

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

    StackedBar_MultiSeriesExample.PNG

     

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

     StackedBar_SortOrderExample.PNG

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"

Answers

  • RabidCougar
    RabidCougar Contributor

    Nick,

     

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


    ** I work for Domo
  • RabidCougar
    RabidCougar Contributor

    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.


    ** I work for Domo
  • nlombardini
    nlombardini Contributor

    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

  • RabidCougar
    RabidCougar Contributor

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


    ** I work for Domo
  • nlombardini
    nlombardini Contributor

    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

     

  • RabidCougar
    RabidCougar Contributor

    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.


    ** I work for Domo
  • nlombardini
    nlombardini Contributor

    @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

     

  • RabidCougar
    RabidCougar Contributor

    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 work for Domo
  • ckatzman
    ckatzman Contributor

    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.

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
  • RabidCougar
    RabidCougar Contributor

    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.


    ** I work for Domo
  • nlombardini
    nlombardini Contributor

    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

  • nlombardini
    nlombardini Contributor
    Answer ✓

    @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

  • RabidCougar
    RabidCougar Contributor

    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!


    ** I work for Domo
  • theCliffs
    theCliffs Member

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

  • ckatzman
    ckatzman Contributor
    Answer ✓

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

    StackedBar_MultiSeriesExample.PNG

     

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

     StackedBar_SortOrderExample.PNG

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

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

    Thanks!

    Dani

  • theCliffs
    theCliffs Member

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

     

    Thanks!

  • jlafee
    jlafee Member

    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.

  • ckatzman
    ckatzman Contributor

    Good deal.

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
This discussion has been closed.

Categories