Background color in Pivot Table based on condition

[#1]

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,

[israrkhan]

try before function in the expression background color..

or share the sample document...will try to help you

[Anonymous]

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

[Anonymous]

hi ,

try this way ,

Under expression --> Background color write this script

if(Credit <> Debit,red())

thanks

[Anonymous]

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

[Gabriel]

Hi,

Try this

Temp:

LOAD * INLINE [

    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:

load

*

,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

[engishfaque]

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

[#8]

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

Thanks,

[Anonymous]

Dear,

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

Thanks

[#10]

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,