6 Replies Latest reply: Nov 7, 2016 9:49 AM by Demian Rebollo RSS

    Stacked Bar Chart Sort Order

    Chris Weldon

      Folks,

       

      I have a stacked bar chart that looks like this

      Capture.PNG

      I really want to order the colours so they are in numerical order.  So we start with "<=80%", then "-80 to 60%" etc, ending up with ">=+80%".

       

      I have uploaded my bands as a separate table as I am doing an Interval Match on the value, so I added a column for DiscountBandSortOrder, but I cant seem to get it to work.  The Dimensions for the chart are Underwriter and Discount Band and the formula for the Measure is:

       

      count({$<QuoteSystemStatus={'Issued','Finalised'},DateFirstQuoteReceived={'>=$(=addmonths(max(DateFirstQuoteReceived),-3))'}>}QuoteID) /

      count(total<Underwriter>{$<QuoteSystemStatus={'Issued','Finalised'},DateFirstQuoteReceived={'>=$(=addmonths(max(DateFirstQuoteReceived),-3))'}>}QuoteID)

       

      So it counts % of the number of QuoteIDs in each DiscountBand compared to the total for that Underwriter in the last 3 months.

       

      What should I use for the expression in the Sort section to get the right order?

       

      Many thanks

       

      Chris

        • Re: Stacked Bar Chart Sort Order
          Demian Rebollo

          I have the same issue... I have tried to even change the sequence in which data is loaded by the script, modified my data tables in Excel... it seems there is no way for the chart developer on how to change the sequence of the segments of the stacked bar are displayed.

          I have played also with the Sorting feature for each dimension, it helped a bit but  it never ends up in a logical way...

          • Re: Stacked Bar Chart Sort Order
            Demian Rebollo

            It seems to me that most likely your problem to sort the bar segments is not as simple, but I think I need to tell you how I solved my sorting issue.

             

            My measures did not have any order. For certain data sets, if I turned off the Auto Sort in the chart, that was enough because they were loaded by the script in order of appearance. But for a certain stacked bar chart that involves a CrossTable feature at load script, this was not working. As I said above, moving around columns in my data set helped a bit but unpredictably. I really needed to make it work, so I added a number to my data set dimensions in front and used the Sorting feature of the chart in Alphabetical order. And it worked just fine.

             

            I went through a lot of scripting try-and-fail, many weeks of that... generated the charts in many different ways, but the simplest of solutions was there a bit hidden and I could not see it from the very beginning.

             

            As I said, this might not be possible for you, but it worked for me.

             

            Good luck!

            • Re: Stacked Bar Chart Sort Order
              bruno bertels

              Hi

              See this approach using match fonction :

              https://community.qlik.com/thread/235804

               

              add this in sortorder panel :

               

              match(YourDimension,'<=80%','-80 to 60%','-60 to 80%','-40 to 60%','-20 to 40%','etc','etc' ... )

               

               

              Hope it helps

               

              Bruno

              • Re: Stacked Bar Chart Sort Order
                Gysbert Wassenaar

                You can try using a calculated dimension using the dual function so you can sort the dimension numerically. See attached example.

                  • Re: Stacked Bar Chart Sort Order
                    Demian Rebollo

                    Wow, Gysbert!
                    That was an elegant way to solve it!

                    However, that is not yet my level - I need to study more to understand how you achieved that with only just a few lines...

                     

                    aggr(

                      dual(

                      only(Customer)

                      ,sum(Amount)

                      )

                    ,Year,Customer)

                     

                    Thanks!

                    (BTW, I did not post this question, so I cannot mark it as solved)