45 Replies Latest reply: Feb 7, 2018 1:40 AM by Vinay Sri Harsha Mattapalli RSS

    color by expression - QlikSense

    Philipp Wesseling

      Hi all,

       

      i am struggeling with a simple line chart. It shows revenue over month - so "Month" would be one dimension. It has two expressions. The one calculates revenue for the current year. The other calculates revenue for the previous year. All no problem.

       

      What i want - and until now couldnt get to: I want the line representing the current year to have a specific color (lets say, red) and the line representing the previous year to have another specific color (lets say, gray).

       

      Can anyone help and explain how this can be done in SENSE ?

       

      Thanks!

      K

        • Re: color by expression - QlikSense
          Sean Donovan

          Hi Philipp,

           

          Left-click the line chart while in edit mode.  The properties of the chart will show up on the right side of your screen.  Click on Appearance --> Colors and legend --> And deselect the option where it says auto colors ON.  Select "By expression" in the menu box that appears, and underneath that you can enter in a expression that can customize the color of the line chart.

           

          Example:

          IF(YearField = '2012', Red(), Green())

           

          Cheers,

          Sean

            • Re: Re: color by expression - QlikSense
              Philipp Wesseling

              Thanks Sean.

               

              Unfortunately this does not seem to do the job, as my dimension "Month" is not unique to a year. So if i set an expression like you suggested, it does not do anything in the chart...

              • Re: color by expression - QlikSense
                Haikuo Yu

                Hi Sean,

                I also have a similar problem about color by expression with QlikSense.

                I use the following expression to assign different color to each REGION (REGION column contains ‘West’, ‘East’, ‘North’, ‘South’)on the map, but my problem is how to also show a legend with REGION type and the corresponding color.

                By the way, I can not use REGION as my map dimension, because I have to use another column (State, State column contains different states name) as my map dimension.

                Could you please give any suggestion?

                Thanks so much for your help!

                 

                If ( REGION = 'West', red(),

                      If ( REGION = 'East', green(),

                           (

                            if ( REGION = 'North', blue(), Yellow() )   

                           )

                      )

                )

              • Re: color by expression - QlikSense
                Steve Street

                Hi, I've seen a few issues around applying specific colours to charts in Sense and wanted to offer an alternative solution. This works well if there are a limited number of dimensions you want to display (e.g. last four years data with each year coloured in gradients of gray with the current year in red, or sales broken down by product category with a total sales amount also displayed in specific colours)

                This can be achieved using a synthetic dimension

                ColorTestSyntheticDim.JPG.jpg

                 

                1. Create the dimension as a synthetic dimension using ValueList. Note: define the dimensions using a useful reference (e.g. 'Current Year') rather than 'Dim1' since this will be displayed in the popups!

                =ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')

                 

                2. Create a single measure with the value to be shown for each Dimension

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim1', SUM({<Year={2011}>}Sales),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim2', SUM({<Year={2012}>}Sales),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim3', SUM({<Year={2013}>}Sales),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim4', SUM({<Year={2014}>}Sales)

                ))))

                 

                3. Set Color by Expression with the colours you want allocated to each Dimension

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim1', argb(80,80,80,80),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim2', argb(160,80,80,80),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim3', argb(220,80,80,80),

                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim4', lightred()

                ))))

                 

                I've attached an application enhancing Josh Good's example. I hope this proves useful.

                  • Re: color by expression - QlikSense
                    Rean Fadyl

                    Nice work Steve! I tried this out and it worked fine. Pity picking a colour for a measure is so hard though.

                    • Re: color by expression - QlikSense
                      Joost Romijn

                      Hi Steve,

                       

                      Is this also possible for a combo chart? Because I can not create a second dimension in a combo chart, the Synthetic Dimension does not seem to be an option here.

                      • Re: color by expression - QlikSense
                        Janani Reddy

                        Hi Steve,

                         

                        Is there a way to display legends for my chart when i use "color by expression" option in qlik sense.

                        How can i achieve it with the above illustration??

                        • Re: color by expression - QlikSense
                          Tobias Lutz

                          Great job!! Thanks a lot.

                           

                          Tobias

                          • Re: color by expression - QlikSense

                            Hello Steve,


                            Further to your solution.

                            I used ValueList the way you said and it really worked .

                            The problem is when I do selection model of the month, for example, the specific month stay in the right color and the rest of the line becomes the color of the second line.

                             

                            Do you have a solution for this?

                             

                            Thanks a lot,

                             

                            gal

                              • Re: color by expression - QlikSense
                                Steve Street

                                Hi Gal - can you send me a screenshot of the issue?

                                  • Re: color by expression - QlikSense

                                    Hi Steve,

                                     

                                    Before the selection-

                                    before.png

                                     

                                    After- (just the Month i chose stay in original color)

                                     

                                    after.png  

                                    Thanks a lot,

                                     

                                    gal

                                      • Re: color by expression - QlikSense
                                        Steve Street

                                        Yes, it will only apply to your current selection. It looks like here you're using Set Analysis to display all the values, independent of any selection made. Selecting the Month of Aug should reduce your line chart to just that month. Perhaps consider creating a Drill-down dimension so that selecting a specific Month will then drill down to the Dates within that Month.

                                        If you use the Date functions (such as MonthName()) on your dates this will also correct your sorting issue (which is currently alphabetical).

                                          • Re: color by expression - QlikSense
                                            Steve Street

                                            Hi again Gal,

                                            For your use case I would recommend a different (and simpler) approach than value list. You have two dimensions (Month and Alarm type) and 1 measure (Number of Alarms) so you can achieve the required result using a colour by expression similar to

                                            if([Alarm]=0,    rgb(82,204,82),

                                            if([Alarm]=1,    rgb(250,104,26),

                                            LightGray(120)))

                                            AlarmApp.PNG

                                            I would also consider using a stacked bar chart so that you can see the overall number of alarms, with a breakdown of which were real / false. In the example I've included you can see I've used a brushing technique so that the month(s) you've selected are highlighted but all months remain visible (but are displayed in LightGray) so that you still see the overall pattern for the year.

                                            AlarmAppBrushed.PNG

                                            I hope this helps.

                                              • Re: color by expression - QlikSense

                                                Hi steve,

                                                 

                                                First, thanks for your help.

                                                 

                                                2 questions:

                                                1. Do you have any idea how to put set Analysis into the expression of color, then the color will ignore the choice of the model?

                                                2. The case that I raised here is an example, the real model can not publish.

                                                In the real model I have two measures, when ValueList is 1 this makes a total of one field and when the ValueList is 2 this makes a total of another field. Do you have any idea for this situation?

                                                 

                                                Thanks a lot,

                                                 

                                                gal

                                                  • Re: color by expression - QlikSense
                                                    Pieter Kuperus

                                                    Hi Gal,

                                                     

                                                    To answer your first question:

                                                    I have taken the Alarms app (that Steve created) and added the Set Analysis for the colour coding - but I used the ARGB function for where the months are not in the current selections. With this, you can make the colours for these months slightly lighter, but it will still follow the same colours.

                                                     

                                                    With regards to your second question. You could use the Pick function to change the expression to be used:

                                                    =pick([Dimension],sum([field 1]),sum([field 2]))

                                                     

                                                    The Pick function will look in the field [Dimension] for sequential numerical values (starting at 1). According to this value, it will then use the expression in the parameter list, that corresponds to the value of the field [Dimension].

                                                     

                                                    If the values in the [Dimension] is not sequentially numeric, then you could list the values with the match function:

                                                    =pick(match([Dimension],'Value 1','Value 2'),sum([field 1]),sum([field 2]))

                                                     

                                                    The Match function will return the position number of the corresponding value in the parameter list, of the row value of [Dimension]. Then the Pick function will then use this position number to choose the corresponding expression.

                                                    Just be careful with mixed case values. For this there is the MixMatch function or change the case to UPPER([Dimension]) or lower([Dimension]).

                                                     

                                                    I hope this helps.

                                                     

                                                    Regards

                                                    Pieter

                                                  • Re: color by expression - QlikSense
                                                    Patrick Ryan

                                                    Hi Steve,

                                                     

                                                    I have followed these instructions and have the colors and the measures I want showing. Now, I would like to show the values and the "labels" on each bar as well.  Is it possible to add "Show: Label" or to define the Label to show and where, as part of an Expression?  Similar to adding ", rgb(192,0.,0)"

                                                     

                                                    Pat

                                                    • Re: color by expression - QlikSense

                                                      Hi.

                                                       

                                                      I have one question related with this:

                                                      You have LightGray for the years that are not selected in the graphic.

                                                       

                                                       

                                                      How can i do to fix the colours independently of the year i select?

                                                       

                                                      Thank you!

                                                       

                                                       

                                                        • Re: color by expression - QlikSense
                                                          Steve Street

                                                          Hi Carla. I'm not sure which chart you're referring to but let's assume its this one.

                                                          In this chart I've used a technique called 'brushing' which still shows you all dimensions but applies specific colouring only to the selected value (in this case Feb 2015). This is useful to highlight a selection but see it in context with your other data. It works by using set analysis on your measure to include the entire dataset (the '1' in the following statement) - Count({1<Alarm=,Month=>}Date)

                                                           

                                                          If you replace the '1' with '$' it will show the current selections so the chart is reduced to only the month and year that was selected. If you want the same colours applied irrespective of what selections are made you can use the technique mentioned above by pieterkup.

                                                          I hope this answers your query

                                              • Re: color by expression - QlikSense
                                                Naresh Guntur

                                                Steve,

                                                 

                                                This is great.

                                                 

                                                But can I use this where the variables have the actual expression stored. It doesn't work for me.

                                                 

                                                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim1', $(Sales2011),

                                                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim2', $(Sales2012),

                                                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim3', $(Sales2013),

                                                if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim4', $(Sales2014)

                                                ))))

                                                 

                                                Cheers,

                                                Naresh

                                                • Re: color by expression - QlikSense
                                                  Amoira MZIOU

                                                  Hi Steve,

                                                   

                                                  I'm trying your magic method with a cumulative sum :

                                                  if(  ValueList('Dim1', 'Dim2')='Dim1', rangesum(Above( (Sum ({<Annee={2017}>} rea_eco)),0,RowNo())),

                                                  if(  ValueList('Dim1', 'Dim2')='Dim2', rangesum(Above( (Sum ({<Annee={2017}>} obj_eco)),0,RowNo()))))

                                                   

                                                  however, the results in the graph are false

                                                   

                                                  any suggestion ?

                                                   

                                                  Thank you.

                                                  • Re: color by expression - QlikSense
                                                    chitraxi raj

                                                    Hey steve this solution worked for me, thank you so much. I have one more functionality to be implemented on top of this. On selection of any month (i have Date instead of month dimension) I have to display data from 2  previous months along with selected month. For Example on the selection of July month, it should show data of May ,June and July.

                                                     

                                                    I have to show data of all dates of this three month on selection of anydate from July. How can we do that?

                                                    I tried this

                                                     

                                                    if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim1', SUM({<Date = {"$(='>=' & Date(AddMonths(BusinessDate, -2)) & '<=' & Date(BusinessDate))"},Year={2011}>}Sales),

                                                    if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim2', SUM({<Date = {"$(='>=' & Date(AddMonths(BusinessDate, -2)) & '<=' & Date(BusinessDate))"},Year={2012}>}Sales),

                                                    if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim3', SUM({<Date = {"$(='>=' & Date(AddMonths(BusinessDate, -2)) & '<=' & Date(BusinessDate))"},Year={2013}>}Sales),

                                                    if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim4', SUM({<Date = {"$(='>=' & Date(AddMonths(BusinessDate, -2)) & '<=' & Date(BusinessDate))"},Year={2014}>}Sales)

                                                    ))))

                                                     

                                                    PS: currently on selecting any date data is coming for one date only

                                                  • Re: color by expression - QlikSense
                                                    Vicio Giglio

                                                    I read all this long thread. Powerless, I chose for a google search with recent results, and I found the right thread.

                                                     

                                                    Color scheme in qliksense for bar chart

                                                     

                                                    That works well for me. If you believe that is the correct answer to this thread, please mark this reply as the solution, and we can help newcomers from reading all this thread.

                                                    • Re: color by expression - QlikSense
                                                      ishan Bhatt

                                                      Hi Philipp,

                                                       

                                                      In Qlik Sense new release provide this type of functionality to give a specific color to the specific measure.

                                                       

                                                      Please see my answer in below link for more detail.

                                                       

                                                      https://community.qlik.com/message/1267037?et=watches.email.thread#1267037

                                                       

                                                      Hope this will help.

                                                      • Re: color by expression - QlikSense
                                                        ishan Bhatt

                                                        Hi Philipp,

                                                         

                                                        In the New version of the Qlik Sense ( i.e 3.2 SR1), it is possible to give a specific color to the multiple expressions (Measures).

                                                         

                                                        I've given a different color in example attached by you in this blog and below is the resulting image.

                                                        Chart.PNG

                                                        To give a specific color to measure follow below steps:

                                                         

                                                        1. Make Expression as Master Item (Measures).

                                                        2. In Master Item (Measures) Choose Specific Color for that expression.

                                                        3.Apply that Master Item Measure directly into Chart Measures.

                                                         

                                                        Below are the Images for the Master Item Setting.

                                                        Master Item.PNG

                                                        Master Item Detail.PNG

                                                         

                                                        Now use this Master Measure Item in Your Measure.

                                                        • Re: color by expression - QlikSense
                                                          Vinay Sri Harsha Mattapalli

                                                          Hi Philipp,


                                                          we have a direct option in sense. add both expressions to master measures there you can select the specific color for each measure.