10 Replies Latest reply: Jan 18, 2015 7:11 PM by Sheetal Panchal

# Background color in Pivot Table based on condition

Hi,

I have 3 fields in my pivot table - Account, Credit/Debit and Amount. Pivot table has following dimension and expression:

Dimensions:

1. Account

2. Credit/Debit

Expression:

1. Sum(Amount)

Is it possible to color the background of a account where the credit and debit are not equal? Below is the requirement.

Thanks,

• ###### Re: Background color in Pivot Table based on condition

try before function in the expression background color..

• ###### Re: Background color in Pivot Table based on condition

Hi,

you can add in the background color  ("+" symbol on the left of your expression) this formula:

if(RowNo()=1 and sum(Amount)<>Below(sum(Amount)) or RowNo()=2 and sum(Amount)<>above(sum(Amount)), Yellow())

or this, more complicated, if user can select Credit/Debit

if((RowNo()=1 and sum(Amount)<>Below(sum(Amount)) or RowNo()=2 and sum(Amount)<>above(sum(Amount))) and GetSelectedCount([Credit/Debit])<>1, Yellow())

Let me know!

BR,

Elena

• ###### Re: Background color in Pivot Table based on condition

Thanks Elena for your reply. However, this highlights all the rows in my pivot table even where Credit = Debit for particular account.

Thanks,

• ###### Re: Background color in Pivot Table based on condition

Dear,

my solution works properly for table you displayed.... If your scenario is more complex, you should attach your qvw or your data.

Thanks

• ###### Re: Background color in Pivot Table based on condition

hi ,

try this way ,

Under expression --> Background color write this script

if(Credit <> Debit,red())

thanks

• ###### Re: Background color in Pivot Table based on condition

i think the above solution proposed by Elena paradoni would serve your purpose

• ###### Re: Background color in Pivot Table based on condition

Hi,

Try this

Temp:

Accounts, Credit/Debit, Amount

Account 1, C, 400

Account 1, D, 500

Account 2, C, 1200

Account 2, D, 1200

Account 3, C, 300

Account 3, D, 350

];

Final:

*

,IF(Previous(Accounts)=Accounts AND Previous([Credit/Debit]) <> [Credit/Debit] AND Previous(Amount) <> Amount,1,0)    AS Check

Resident Temp

;

DROP Table Temp;

And in the chart go to Chart -> Properties -> Expression -> Background -> Enter this

=if(Check =1,WhateverColourYouWant())

Click OK

Hope this helps

• ###### Re: Background color in Pivot Table based on condition

Dear Sheetal,

There are so many ways to apply background color in pivot table cell. Most of them these two ways are better than others.

1.  Colormix Wizard...

Right Click on the pivot table > Properties > Expression tab > Edit Expression > File > Colormix Wizard... > Fill      all the options values as required to you > Finish.

2.  If (Condition, then, else)

as mentioned by many community members in this thread as given above.

I recommended you to do this by Colormix Wizard..., Colormix Wizard... create complex expression but you don't need to edit because it works by itself and it will be generic module for your cell color.

I hope, you really enjoy this new way to apply colors.

Kind regards,

Ishfaque Ahmed

• ###### Re: Background color in Pivot Table based on condition

Hi Ishfaque,

Thanks for your reply. I am aware of all the methods you mentioned above - Colormix as well as setting Background color using condition. However, since I want only one color based on one condition, Background color would be a better option. I tried below but does not give the desired output for my requirement. Let me know if you have some other option or any modifications to the below condition:

if([Account] = Below([Account]) and [Debit/Credit] <> Below([Debit/Credit]) and sum(Amount) <> Below(sum(Amount)),Yellow())

Thanks,

• ###### Re: Background color in Pivot Table based on condition

To get the desired output, I multiplied the amount by -1 where it was Debit(which should have been negative in the data) and then enabled Partial Sum on the Account column. I then used below expression. This highlights the row where Total is not 0 (Credit <> Debit)

if(RowNo() = 0 and sum(Amount) <> 0, Yellow())