11 Replies Latest reply: Apr 1, 2016 7:06 AM by Supriya Ranganath RSS

    QlikView Chart Color Logic

    Reed Perry

      Hello,

       

      I am trying to change colors in a line chart based on dynamic criteria.

       

      I have a chart with a field called Business. In that field some of the values are: Shoes, Socks, Hats

      Other values in that field include: Shoes (O), Socks (O), Hats (O)

       

      I want to make the values without an '(O)' at the end be a solid color in the chart, and the one's with an '(O)' be a more opaque version of the original color.  It is important that this is done through automated logic.  It is also important to note that because there are other dimensions in this chart I cannot utilize a new field distinguishing between the values with and without '(O)'.

       

      My current idea:

      Inline load the color values or load them from Excel as R, G, and B variables and somehow map them to each Business field value.  This would result in an expression that says "if the Business is 'Shoes' then display it as rgb(Rshoes, Gshoes, Bshoes) for 'Shoes' and if the Business is 'Shoes (O)' then display it as argb(100, Rshoes, Gshoes, Bshoes)."

      Written in the background color for the expression as:

      If(Business = 'Shoes' , rgb(R,G,B), If(Business = Business & ' (O)', argb(100, R,G,B))

       

      I am unsure as to what method to use to load in the color values and how to make sure that the portion of the expression containing 'If(Business = Business & ' (O)''  functions properly. 


      Thank you for any help!!



      Update:

      I am pulling in the color values from Excel columns.

      My formula for background color in the expression now reads as "ARGB(ColorA,ColorR,ColorG,ColorB)"

      This expression colors everything correctly if I manually put in an argb value for all business values.


      I am still trying to figure out a way to auto generate the colors for businesses with the 'O' on the end.

      Thanks!


      Correct Answer Reply:


      Thanks everyone for the great replies!

       

      Marco, I used your iteration logic as a launching point to dynamically assign the colors.  I ended up inline loading a list of colors as such:

      ColorPalette.PNG

      and then looping through my data to dynamically assign each of those colors based on certain criteria.

       

      Thanks again for the help!

       

      Message was edited by: Reed Perry

        • Re: QlikView Chart Color Logic
          Massimo Grossi

          maybe

           

          if(WildMatch(Business, '*(O)'), rgb(R,G,B), argb(100,R,G,B))

          • Re: QlikView Chart Color Logic
            Marco Wedel

            Hi,

             

            one solution could be:

             

            QlikCommunity_Thread_204912_Pic1.JPG

            QlikCommunity_Thread_204912_Pic2.JPG

             

            QlikCommunity_Thread_204912_Pic6.JPG

             

            QlikCommunity_Thread_204912_Pic3.JPG

             

            or

             

            QlikCommunity_Thread_204912_Pic5.JPG

             

            QlikCommunity_Thread_204912_Pic4.JPG

             

             

            tabBusCol:
            LOAD Business&If(IterNo()=2,' (O)') as Business,
                ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol
            INLINE [
                Business, R, G, B
                Shoes, 50, 100, 150
                Socks, 100, 150, 50
                Hats, 150, 50, 100
            ] While IterNo()<=2;
            
            tabSales:
            LOAD *,
                Money(Ceil(Rand()*1000)) as Sales
            INLINE [
                Business
                Shoes
                Socks
                Hats
                Shoes (O)
                Socks (O)
                Hats (O)
            ];
            

             

            hope this helps

             

            regards

             

            Marco

              • Re: QlikView Chart Color Logic
                Reed Perry

                Marco,

                 

                Thanks for your thorough answer!  That portion of the script in lines 2-3 is exactly what I needed!

                 

                The only other thing is that I would like to avoid inline loading the colors... I am hoping to store the solid colors in an excel spreadsheet so they can be maintained easier.

                 

                My spreadsheet looks likes:

                Column A: Business

                Column B: Value for R in rgb

                Column C: Value for G in rgb

                Column D: Value for B in rgb

                 

                Is there a way I could use the same logic but instead of lines 4-8 I'd load from Excel?

                 

                Thanks again for the help!

                • Re: QlikView Chart Color Logic
                  Reed Perry

                  The next step for this color problem is that after I click into a Business such as "Hats" I get a list of products displayed.

                  My chart is designed with a conditional dimension so that when I drill into a Business the chart changes the dimension to Products.

                   

                  For example:  Once I choose "Hats" as a Business the chart displays values including: Top hats, Baseball hats, Hard hats, Top hats (O), Baseball hats (O), Hard hats (O).

                   

                  For these values I do not want to have to set a specific color for every single one so I am hoping to make the solid color randomly generated and then the corresponding (O) value to have a lighter color.  So "Top Hats" may randomly appear as Red and if so then "Top Hats (O)" would appear as Light Red.

                   

                  One more thing worth noting is that just because a field like "Top hats" exists does not mean "Top hats (O)" exists and vice versa.

                    • Re: QlikView Chart Color Logic
                      Marco Wedel

                      Hi,

                       

                      maybe like this?

                       

                      QlikCommunity_Thread_204912_Pic7.JPG

                       

                      QlikCommunity_Thread_204912_Pic8.JPG

                       

                      QlikCommunity_Thread_204912_Pic9.JPG

                       

                       

                       

                      tabBusCol:
                      LOAD Business&If(IterNo()=2,' (O)') as Business,
                          ARGB(Pick(IterNo(),255,100),R,G,B) as BusCol
                      FROM QlikCommunity_Thread_204912.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
                      While IterNo()<=2;
                      
                      tabSales:
                      LOAD *,
                          Money(Ceil(Rand()*1000)) as Sales
                      INLINE [
                          Business, Product
                          Shoes, Boots
                          Shoes, Sneakers   
                          Socks, low-cut
                          Socks, ankle-dress     
                          Hats, Top hats
                          Hats, Baseball hats
                          Hats, Hard hats       
                          Shoes (O), Boots (O)
                          Shoes (O), Sneakers (O)   
                          Socks (O), low-cut (O)
                          Socks (O), ankle-dress (O)
                          Hats (O), Top hats (O)
                          Hats (O), Baseball hats (O)
                          Hats (O), Hard hats (O)     
                      ];
                      
                      tabProdCol:
                      LOAD Product&If(IterNo()=2,' (O)') as Product,
                          ARGB(Pick(IterNo(),255,100),ProdCol>>16 bitand 255,ProdCol>>8 bitand 255,ProdCol bitand 255) as ProdCol
                      While IterNo()<=2;
                      LOAD Distinct
                          Product,
                          Color(AutoNumber(Product)) as ProdCol
                      Resident tabSales
                      Where not Product like '* (O)';
                      

                       

                       

                      hope this helps

                       

                      regards

                       

                      Marco

                        • Re: QlikView Chart Color Logic
                          Rob Wunderlich

                          If you want to avoid pre-loading the values in the script, you can compute the color at runtime using a background expression of:

                           

                          if(Business LIKE '*(O)'

                          ,color(FieldIndex('$(=GetCurrentField(BizProduct))'

                            ,left($(=GetCurrentField(BizProduct)),len($(=GetCurrentField(BizProduct)))-4)))

                          bitand argb(100,255,255,255)

                          ,color(FieldIndex('$(=GetCurrentField(BizProduct))',$(=GetCurrentField(BizProduct))))

                          )

                           

                          Note if you have more than 18 values for a field, you'll have to use mod(fieldindex,18)+1 to get one of the 18 color palette values.

                           

                          More info here

                          A Color Trick | Qlikview Cookbook

                           

                          -Rob

                          http://masterssummit.com

                          http://qlikviewcookbook.com

                            • Re: QlikView Chart Color Logic
                              Supriya Ranganath

                              I faced a similar situation. But I have 3 dimensions in my case: Company, Metric and Year. The sum(value) of the metric is my expression. I have chosen year as my primary and Company as secondary dimension and the metric is stacked. I get only 2 colors when i use an expression like this:

                              if(Company=SubField($(vCompany),’,’,1),argb(75,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)) , If(Company=SubField($(vCompany),’,’,2),argb(100,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,3),argb(125,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),

                              If(Company=SubField($(vCompany),’,’,4),argb(150,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,5),argb(175,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,6),argb(200,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),If(Company=SubField($(vCompany),’,’,7),argb(255,255,255,255) bitand color(FieldIndex(‘Metric’,Metric)),color(FieldIndex(‘Metric’,Metric)))))))))

                              Please help me..

                            • Re: QlikView Chart Color Logic
                              Reed Perry

                              Thanks everyone for the great replies!

                               

                              Marco, I used your iteration logic as a launching point to dynamically assign the colors.  I ended up inline loading a list of colors as such:

                              ColorPalette.PNG

                              and then looping through my data to dynamically assign each of those colors based on certain criteria.

                               

                              Thanks again for the help!