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 ?

       

      Thanks

        • Pivot tables conditional colours
          Patrick Laredo

          hi,

           

          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

               

              Anne

              • 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 ?

                 

                Thanks

                  • Pivot tables conditional colours
                    Patrick Laredo

                    hey,

                    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 ?

                        Thanks