7 Replies Latest reply: Jun 7, 2011 12:01 PM by Anne Duffy RSS

    Pivot tables conditional colours

      Hi Guys


      I have a pivot table built , where I have 3 colour conditions I wish to apply - intially I was going to do this on Visual Cues tab, but as there are 3 conditions I dont think I can.


      So I was going to do it in the expressions, I have tried,


      if ([total] < 100, (RGB 0,128,0),if ([total] > 100 and < 115, (RGB 255,255,0),if([total] > 115,(RGB 255,0,0))))


      But its just not working the condition is


      Less than or equal too 100 , text color Green

      between 101 and 115 , text Orange


      and greater than 115 text is Red.


      Can anyone please help ?



        • Pivot tables conditional colours
          Patrick Laredo



          this should do it


          if ( [total] <= 100, RGB (0,128,0),                 // if less than 100 than green

               if ([(total] <= 115, RGB (255,255,0),        // else if less than 115 then orange

                    RGB (255,0,0)))                             // else red



          rgb() is a function so teh parenthisis go around the arguments.


          bewrae that id your total has got decimal places you will have to tweak the conditions.

            • Pivot tables conditional colours

              Pat Thanks so much



              • Pivot tables conditional colours

                Hey Pat


                I cant quite get the above to do what I want , I have the chart formatted as decimals and it doesnt seem to work I have tried changing the range to 0.1 and 0.115, but it stil isnt working can you please advise ?



                  • Pivot tables conditional colours
                    Patrick Laredo


                    what do you mean you cannot get it to do exactly what you want?


                    What kind of values are you getting in the [total] as this is what we are comparing against? Can you show some of those values here?


                    Is it a decimal? why have you changed the range to 0.1, 0.115? Is [total] a percentage? or do you have values like 100.1, 115.05 etc. ?


                    you can remove the decimal part in the conditional expression by doing this floor([total]) without touching your actual expression.


                    Sometimes these color codings can be misleading especially on the boundaries as the underlying number may be different to the displayed figure - due to rounding/formatting etc.


                    could you post an example of your qvw?

                      • Pivot tables conditional colours

                        Hi pat


                        I orignally used this statement on a pivot chart loaded from excel where it ws formatted as a number , as ws the chart in Qlikview.


                        I wish to use it now in another Pivot chart where the data in Excal is formatted as Percentage and in chart its as percentage with 1 decimal place .


                        This is the statement as amended:



                        if ([2.1.2 FTE Manpower v Actual (Budget = 107)] <= 0.1, RGB (0,255,0), if ([2.1.2 FTE Manpower v Actual (Budget = 107)] <= 0.115, RGB (255,255,0),RGB (255,0,0)))



                        It is resulting in all the background colours being RED ( Which is the or else part of the condition) 

                        Do you reckon its something with how the data is formatted in the load ?