27 Replies Latest reply: Nov 23, 2016 3:13 PM by Benjamin Wildmon RSS

    How to use custom colours in a stacked bar chart

    David Brandstädter

      Hi there,

       

      I am struggling to colour a stacked bar chart in custom colours. If you take belows chart as an example, I would like to e.g. replace the green with black, the purple with turquoise etc.

      This example chart is auto-colour by QlikSense in the multicolored>12 colors mode.

      stacked-bar chart.PNG

       

       

      I defined my custom colours as e.g. "kGreen", "kBlue" and usually use nested if-expressions to custom-colour my charts.

      With the stacked bar chart, however, the issue is that my six different measures are six columns of the same facts table and the if-statements don't work.

       

       

      Thank you very much for your help!

        • Re: How to use custom colours in a stacked bar chart
          santhosh k

          You can use your color conditions in the expression box under 'Colors and legend' > Custom colors > By Expression

          • Re: How to use custom colours in a stacked bar chart
            David Brandstädter

            Hi all,

             

            thank you for your replies. I have, however, not been able to fix the problem yet.

             

            I do not want to colour each stack differently, I merely would like to "re-colour" the segments for each stacks:

             

            stacked-bar chart__v2.PNG

             

            The simplified underlying data model for the graph looks like the following:

             

            MonthK1E1B1B2B3Bx
            April544432
            May333223
            June151312131412
            July781010910
            August667786
            September554355

             

             

            Hope this makes it clear!

            • Re: How to use custom colours in a stacked bar chart
              Thomas Cullinane

              if all the segments are always going to be present you could just change the appropriate colours in the colour tab in the chart to the ones you want.

               

              If you want specific segments to always be displayed as specific colours then you need the if statement, which is probably best done in a variable.

               

              Here is an example; (I used the vName to define the chart second dimension so it can be changed b the user, hence the multiple options for the colour assignment)

               

              if(match($(vName),'Closed'),RGB(255,248,210),

              if(match($(vName),'NA','Resolved'),RGB(255,247,188),

              if(match($(vName),'Low','PID Requested','Tier 1','Preventative maintenance'),RGB(254,227,145),

              if(match($(vName),'Medium','Open','Tier 2'),RGB(254,196,79),

              if(match($(vName),'High','Waiting On A Task','Tier 3','Request For Information'),RGB(254,153,41),

              if(match($(vName),'Awaiting User Feedback','Tier 4'),RGB(236,112,40),

              if(match($(vName),'Onhold','Incident'),RGB(204,76,2),

              if(match($(vName),'Critical','With Vendor'),RGB(153,52,4),

              ))))))))

              • Re: How to use custom colours in a stacked bar chart
                Demian Rebollo

                David,

                 

                I have also been struggling with this topic.

                 

                I followed the hints that others have provided you and after some attempts I got what I think you need.
                2015-10-06 16_23_41-Qlik Sense Desktop.png

                To explain what others already told you in this thread a bit further:


                You create a table in the Load Script (see attached QVF file) with two columns: Segment and Values.
                Then you use Month and Segment as Dimensions and Values as Measure.

                 

                [StackedBarChartTest]:

                LOAD [Month],

                  [K1],

                  [E1],

                  [B1],

                  [B2],

                  [B3],

                  [Bx]

                FROM [lib://Qlik/StackedBarChart.xlsx]

                (ooxml, embedded labels, table is StackedBarChartTest);

                 

                CrossTable(Segments, Values)

                LOAD Month,

                     K1,

                     E1,

                     B1,

                     B2,

                     B3,

                     Bx

                FROM

                [lib://Qlik/StackedBarChart.xlsx]

                (ooxml, embedded labels, table is StackedBarChartTest);

                 

                // Chart Colours

                Let K1_Colour       = RGB(255,215, 15);

                Let E1_Colour       = RGB(255,187, 15);

                Let B1_Colour       = RGB(179,131, 12);

                Let B2_Colour       = RGB(179,106, 12);

                Let B3_Colour       = RGB(171,177, 11);

                Let Bx_Colour       = RGB(100,100,100);

                 

                To change the colours:

                 

                if(match(Segments,'K1'),K1_Colour,

                if(match(Segments,'E1'),E1_Colour,

                if(match(Segments,'B1'),B1_Colour,

                if(match(Segments,'B2'),B2_Colour,

                if(match(Segments,'B3'),B3_Colour,

                if(match(Segments,'Bx'),Bx_Colour,

                ))))))

                 

                Hope this helps .

                 

                Sadly for me, this DOES NOT WORK for a Dimension created in the Master items section, which was what I have been looking for.

                 

                Once you add two Dimensions to a bar chart, then you can plot only ONE Measure.

                 

                If you have a Master item - Dimension, then you're stuck with whatever colours Qlik Sense gives you, no way to change them. Or at least I haven't found a solution in the Community so far and all my attempts have failed. No pick(match()...) works. Or maybe it is an advanced topic and I am not yet there . I tried the Colour table suggested to you and it did not work... Too complicated for a newbie perhaps?

                  • Re: How to use custom colours in a stacked bar chart
                    Nasser Melloul

                    Hello,

                     

                    I am struggling with a "problem" in Qlikview Could someone help me please ?

                    I have created a stacked bar chart. 2 expressions and 2 dimensions, one is year-month and one is calculated (3 VALUES, 'achieved', 'justmissed' and 'missed'). I would like to display 'achieved' in green, 'justmissed' in yellow and 'missed' in red.

                    To achieve this, I have created 2 variables:

                    1. vBenchLabel that returns 'Achieved', 'Just Missed', or 'Missed'
                    2. vBenchColor=pick(match($(vBenchLabel),'Missed','Just Missed','Achieved'),lightred(),yellow(),lightgreen())

                    and the definition back ground color of the first expression is $(vBenchColor)

                    BUT the 3 segments are RED.

                    thank you very much

                    Nasser

                      • Re: How to use custom colours in a stacked bar chart
                        Sangram Reddy

                        Hi Nasser,

                         

                        Use these expressions:

                        Dimension 1: year-month

                        Dimension  2: valuelist( 'achieved', 'justmissed' and 'missed')

                         

                        Measure : if(valuelist( 'achieved', 'justmissed' and 'missed') = 'achived',<expression1>,

                                       if(valuelist( 'achieved', 'justmissed' and 'missed') = 'justmissed',<expression2>,

                                       if(valuelist( 'achieved', 'justmissed' and 'missed') = 'missed',<expression3> )))

                         

                        Colour Expression:

                                       if(valuelist( 'achieved', 'justmissed' and 'missed') = 'achived',lightgreen(),

                                       if(valuelist( 'achieved', 'justmissed' and 'missed') = 'justmissed',yellow(),

                                       if(valuelist( 'achieved', 'justmissed' and 'missed') = 'missed',lightred() )))

                          • Re: How to use custom colours in a stacked bar chart
                            Nasser Melloul

                            Hi Sangram,

                             

                            thank you very much for you quick answer.

                            I am trying to implement your solution but I don't know what is the expression1, expression2, expression3 in the measure.

                            I have 2 measures

                            1. count(action) / count(action TOTAL year) ==> percentage used for the size of each stacked segment
                            2. count(action)==>used to display the number of action

                            so what do you mean about expression1, expression2, expression3 ?

                            Many thanks

                          • Re: How to use custom colours in a stacked bar chart
                            Sangram Reddy

                            Hi Nasser,

                             

                            Is it possible to attach a sample qvf so it will be easier to explain?

                             

                            Thanks

                            Sangram

                        • Re: How to use custom colours in a stacked bar chart
                          Himanshi Dubey

                          hiii,

                             you can also use colors tab in chart properties to change the colors as you want.

                           

                          Regards,

                          Himanshi Dubey

                          • Re: How to use custom colours in a stacked bar chart
                            Vincent Matyi

                            Hello,

                             

                            I am new to the group and this is my first post/question.

                             

                            I'd like to ask what is the purpose of custom colors in stacked bar charts if you are not able to display a legend of what each of those colors represent? How have you worked around this usability issue?

                             

                            Thank you,

                            Vincent

                            • Re: How to use custom colours in a stacked bar chart
                              Benjamin Wildmon

                              Hi. I'm trying to color Master Measures in a combo stacked bar chart. I've tried the various methods mentioned in this thread but I'm not having any luck. Either everything turns to one color or isn't colored at all.

                               

                              Example of one of the measures -

                              ***************************************

                              (sum( {<[Master Plan Ship Year] = {$(=MAX([Master Plan Ship Year]))}, [Plant Loading Record Type] = {'Booked'}>} [Shop Hours: Sales Total_WESESTIMATEDSHOPHOURS Code])

                               

                              ***************************************

                               

                              The Dimension is a drill down Master Dimension with these levels:

                              Quarter

                              Month

                              Project

                               

                               

                              I've tried Pick(Match([Plant Loading Record Type] ,'Booked'),lightblue()) and I've tried loading in an Inline table housing the [Plant Loading Record Type] and an RGB code. Nothing seems to work. I've also used

                              "ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,$(vColorMixDark), $(vColorMixLight),$(vColorMixMiddle))".

                              It colors the bars but not the individual measures within the bar.



                              Any tips or advice?

                               

                              Capture.PNG