22 Replies Latest reply: Jun 8, 2011 11:34 AM by Anne Duffy RSS

    Background colour set in expression

      Hi Guys

       

      I have a pivot table and I wish to set conditional background colours , but I need to set it on multiple selections,

       

      Basically if 

                        Field [Report] = CBIRE AND [Total] < 70 go Green

                        Field [Report] = CBIRE AND [Total] < 110 go Orange

                        Field [Report] = CBIRE AND [Total] > 70 go Red

       

      AND

                        

                        Field [Report] = ROI AND [Total] < 250 go Green

                        Field [Report] = ROI AND [Total] < 500 go Orange

                        Field [Report] = ROI AND [Total] > 500 go Red

               

       

      I actually have another 4 rules based on different options in Report and Total to add in. But I have tried the first part using

       

      if(Report='CBINT' and Total <=70,RGB (0,255,0),if(Report='CBINT' and Total<=110,RGB (255,255,0),RGB (255,0,0)))

       

      but this is turning them all red regardless of what is filtered or not.

       

      Can anyone please help ?

       

      Thanks

       

      A

        • Background colour set in expression
          Colin Hancox

          Using exactly your formula I get green, yellow, red.

           

          It might be obvious, but in your first set of data you have CBIRE and in your calculation you have CBINT.  Is it not that?

            • Background colour set in expression

              Would you believe the samples that I had loaded had only RED range values loaded ( I know its often the simpliest issues that cause the greatest hassle !!!)

               

              I've just started writing it with all 6 conditions , so please feel free to watch this space cause I reckon I will have more questions when Im joining all the ifs together

               

              Thanks

                • Background colour set in expression
                  Colin Hancox

                  Just make sure you have as many closing brackets as opening brackets and you'll be fine.

                    • Re: Background colour set in expression

                      Hey Colin

                       

                      I have used the following and it works fine for all the drill downs ( So when I select a report).

                      But i need a condition where no reports are selected ( So basically the sum of each of the reports) the formula in bold in that portion but it does not seem to work , can you see if I have made a mistake ?

                       

                       

                      Or is there a way of saying if field is not selected ?

                       

                      Thanks


                      Anne

                       

                      if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if([Total]<=920,RGB (0,255,0),if([Total]<=1406,RGB (255,255,0),RGB (185,0,0))))))

                        • Background colour set in expression

                          Hi Colin

                           

                          I also took the opposite and tried when all Reports were selected see below in bold and again got same result all fields were red ,

                           

                           

                           

                          if(Report='CBINT' and [Total]<=600,RGB (0,255,0),if(Report='CBINT' and [Total]<=800,RGB (255,255,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),if(Report='IRE' and [Total]<=50,RGB (0,255,0),if(Report='IRE' and [Total]<=75,RGB (255,255,0),if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=920,RGB (0,255,0),if(Report='ROI Bank' and Report='CBINT' and Report='IRE'and Report='PAYMENTS/I&I' and [Total]<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

                            • Background colour set in expression
                              Colin Hancox

                              What are you trying to highlight in red, a total on the chart, or each report?  If it is a total, try this:

                               

                              =if(Report='CBINT' and [Total]<=600,RGB (0,255,0),

                              if(Report='CBINT' and [Total]<=800,RGB (255,255,0),

                              if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,255,0),

                              if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,255,0),

                              if(Report='IRE' and [Total]<=50,RGB (0,255,0),

                              if(Report='IRE' and [Total]<=75,RGB (255,255,0),

                              if(Report='ROI Bank' and [Total]<=250,RGB (0,255,0),

                              if(Report='ROI Bank' and [Total]<=500,RGB (255,255,0),

                              if(sum([Total])<=920,RGB (0,255,0),

                              if(sum([Total])<=1406,RGB (255,255,0),RGB (185,0,0)))))))))))

                                • Background colour set in expression

                                  Hi Colin

                                   

                                  I tried the above, what I wish to happen is , this formula  is on a Pivot Chart,on a list box if I select CBINT and the Total is 700 I wish for the field to go Orange or if I select IRE and its 700 it will go red . ( Using my original statement this was happening)

                                   

                                  ( using for example there is only 2 reports) If I have not selected anything in the List box for report ( the pivot chart shows the totals) it uses the condition I would expect it to go Orange as the total is 1400 which under the last condition of the formula is < 1406 .

                                   

                                   

                                  Whats happening with the above statement is when no fields are selected it is remaining green , and on the drill down only the ROI is actually working now

                                   

                                   

                                  Thanks so much for your time on this

                                    • Background colour set in expression
                                      Colin Hancox

                                      No problem.  I'm not sure I really understand your data - what is the Dimension on your pivot table?  Is it Report, or some other dimension?  Can you attach an example with your data structure and the chart.

                                       

                                      I think the problem is that you are trying use the same expression to change the colour for each row and for the total.  What might work better is to include the option for red in each condition, e.g.

                                      if(Report='IRE' and [Total]<=50,RGB (0,255,0),

                                      if(Report='IRE' and [Total]<=75,RGB (255,255,0),

                                      if(Report='IRE' and [Total]>75,RGB (185,0,0),

                                       

                                      Then the last condition will only apply to the total.

                                • Background colour set in expression
                                  Piet Hein van der Stigchel

                                  You can check the nr of selections (or none) like this:

                                  if(GetSelectedCount(Field)=0,...,...)

                                    • Background colour set in expression

                                      Hi Stigchel,

                                       

                                      I have tried your suggestion , so what I wish to happen is that when no selection is Report the conditions <920 Green , <1406 Orange and >1406 Red. But now no condtional formating works at all, I know the first part of the formula works , as I've checked it . its just getting the condition when no Reports are selected from the drop down ( to be honest in some ways its actaully when all reports are selected in the data tho !!! perhaps this is the issue with the getselected fields !!

                                       

                                       

                                      Can you help ?

                                       

                                       

                                      if(Report='CM Banking INT' and [Total]<=600,RGB (0,127,0),if(Report='CM Banking INT' and [Total]<=800,RGB (255,128,0),if(Report='CM Banking INT' and [Total]>800,RGB (255,0,0),if(Report='PAYMENTS/I&I' and [Total]<=70,RGB (0,127,0),if(Report='PAYMENTS/I&I' and [Total]<=110,RGB (255,128,0),if(Report='PAYMENTS/I&I' and [Total]> 110,RGB (255,0,0),if(Report='IRE' and [Total]<=50,RGB (0,127,0),if(Report='IRE' and [Total]<=75,RGB (255,128,0),if(Report='IRE' and [Total]> 75,RGB (255,0,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=250,RGB (0,127,0),if(Report='AIB Bank/AIB(UK)' and [Total]<=500,RGB (255,128,0),if(Report='RAIB Bank/AIB(UK)' and [Total]> 500,RGB (255,0,0),if(GetSelectedCount(Report)=0 and[Total]<=920,RGB (0,127,0),if(GetSelectedCount(Report)=0 and [Total]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

                                        • Background colour set in expression
                                          Piet Hein van der Stigchel

                                          There is a space missing between "and[Total]" in your previous to last post. Add a space and the expression will work for any single or none selection in report.

                                            • Background colour set in expression
                                              Piet Hein van der Stigchel

                                              This still leaves you with the problem of multiple selections in report. You might want to control this by allowing only one selection (e.g. the option "always one selected value"). If you need the total as selection, load a crosstable with the report values and a total.

                                                • Background colour set in expression

                                                  Hi Stigchel,

                                                   

                                                  Due to this specific data there will only ever be one report selected , so I think if I can write the condition that says when no [RAG Code] follow condition < 920 Green , >1406 Orang < 1406 Red,

                                                   

                                                  Going to try that now

                                                    • Re: Background colour set in expression
                                                      Piet Hein van der Stigchel

                                                      In the above expression one "if " is incorrectly broken off (line 8). I have corrected and used this expression succesfully in a document of mine (offcourse replacing your fields with ones that exists in mine)

                                                        • Re: Background colour set in expression

                                                          Is the condition working when no fields are selected?

                                                           

                                                          Mine are all just Red

                                                           

                                                          ( the above was just a paste error into this forum )

                                                            • Background colour set in expression
                                                              Piet Hein van der Stigchel

                                                              Working with both none or one selected...

                                                                • Re: Background colour set in expression

                                                                  Haha OK thats it Ive officially given up Its genuinely not happening for me

                                                                   

                                                                  sample 1.bmp

                                                                   

                                                                  See Fig 1 In total column I would have wanted Jan ,Feb , Mar and May to be Green as they are less that 920 and only April Red.

                                                                   

                                                                  Fig 2 shows that when i drill into a Report it works fine !!!

                                                                   

                                                                  I've literally gone crazy looking at this

                                                                  Below is the exact condition.

                                                                  Thanks so much for your time and help Stigchel ,

                                                                   

                                                                  if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0),
                                                                  if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0),
                                                                  if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0),
                                                                  if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0),
                                                                  if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0),
                                                                  if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0),
                                                                  if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0),
                                                                  if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0),
                                                                  if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0),
                                                                  if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0),
                                                                  if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0),
                                                                  if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0),
                                                                  if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0),
                                                                  if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

                                                                    • Re: Background colour set in expression

                                                                      Hi there

                                                                      I have resigned myself to the fact that I will not get the conditional colors on the totals ,

                                                                      So I have reverted back to your original suggestion Stigchel, in the script I put an if statement

                                                                       

                                                                      if([3.1.3 Reconciliations - total number of outstanding items]<70,'Green',if([3.1.3 Reconciliations - total number of outstanding items]>70 and [3.1.3 Reconciliations - total number of outstanding items]<110,'Orange','Red')) as [RAG Code],

                                                                       

                                                                      So created a column that determines value based on Totals field, then using this column ( RAG Code) wrote the following condition into my expression

                                                                      if([RAG Code]='Green' and Report='CM Banking INT'
                                                                      or [RAG Code]='Green' and Report='PAYMENTS/I&I'
                                                                      or [RAG Code]='Green' and Report='AIB Bank/AIB(UK)'
                                                                      or [RAG Code]='Green' and Report='IRE', RGB(0,127,0),
                                                                      if([RAG Code]='Orange' and Report='CM Banking INT'
                                                                      or [RAG Code]='Orange' and Report='PAYMENTS/I&I'
                                                                      or [RAG Code]='Orange' and Report='AIB Bank/AIB(UK)'
                                                                      or [RAG Code]='Orange' and Report='IRE', RGB (255,128,0),
                                                                      if([RAG Code]='Red' and Report='CM Banking INT'
                                                                      or [RAG Code]='Red' and Report='PAYMENTS/I&I'
                                                                      or [RAG Code]='Red' and Report='AIB Bank/AIB(UK)'
                                                                      or [RAG Code]='Red' and Report='IRE', RGB (255,0,0),
                                                                      RGB(255,255,255))))

                                                                       

                                                                      So the colors will only change when I select a report , failing that they will stay white, its not ideal but its a workaround.

                                                                      Cant thank you enough for all your help and time

                                                                      Anne

                                                                       

                                                                       

                                                                • Background colour set in expression
                                                                  Piet Hein van der Stigchel

                                                                  You can also load the values for R and G and B in seperate fields instead of a textual colour coding and in the color expression use:

                                                                  =RGB(R,G,B)

                                                                  If you have named these fields "R", "G" and "B"

                                                            • Re: Background colour set in expression

                                                              Hi Stigchel

                                                              The [Total] column is actually got some crazy name so for the sake of easy reading on the forum I have being pasting total across the name Below is the actual formula , I think there actually is a space there so I am double stumped !!!

                                                               

                                                              if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=600,RGB (0,127,0),

                                                              if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]<=800,RGB (255,128,0),

                                                              if(Report='CM Banking INT' and [3.1.3 Reconciliations - total number of outstanding items]>800,RGB (255,0,0),

                                                              if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=70,RGB (0,127,0), i

                                                              f(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items]<=110,RGB (255,128,0),

                                                              if(Report='PAYMENTS/I&I' and [3.1.3 Reconciliations - total number of outstanding items] >110,RGB (255,0,0),

                                                              if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=50,RGB (0,127,0),

                                                              if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]<=75,RGB (255,128,0),

                                                              if(Report='IRE' and [3.1.3 Reconciliations - total number of outstanding items]>75,RGB (255,0,0),

                                                              if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=250,RGB (0,127,0),

                                                              if(Report='AIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]<=500,RGB (255,128,0),

                                                              if(Report='RAIB Bank/AIB(UK)' and [3.1.3 Reconciliations - total number of outstanding items]>500,RGB (255,0,0),

                                                              if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=920,RGB (0,127,0),

                                                              if(GetSelectedCount(Report)=0 and [3.1.3 Reconciliations - total number of outstanding items]<=1406,RGB (255,128,0),RGB (255,0,0)))))))))))))))

                                                • Background colour set in expression
                                                  Piet Hein van der Stigchel

                                                  It also might be a good idea to load the conditions/limits in fields linked to the report field, say 'Lower' and 'Upper'. You could then have a variable checking which report is selected, or none, and set the values.

                                                   

                                                  i.e. Value for vLower=if(GetSelectedCount(Report)=0,vLower=920,vLower=Lower)

                                                   

                                                  Expression for color:

                                                  if([Total]<=vLower,RGB (0,255,0),

                                                  if([Total]<=vUpper,RGB (255,255,0),

                                                  if([Total]>vUpper,RGB (185,0,0))))

                                                    • Background colour set in expression

                                                      Hi Stigchel

                                                       

                                                      I have used your rational and in ths script put the following

                                                       

                                                      if([Total]<70,'Green',if([Total]>70 and [Total]<110,'Orange','Red')) as [RAG Code],

                                                       

                                                      so now in my condtional expression I can write it as

                                                       

                                                      if([RAG Code]='Green',RGB (0,127,0),if([RAG Code]='Orange', RGB (255,128,0),RGB(255,0,0)))

                                                       

                                                      Which again works great so long as I have a Report selected, can you help me add a condition that states when Reports is not specified ?

                                                       

                                                      Thanks