
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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())


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try before function in the expression background color..
or share the sample document...will try to help you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi ,
try this way ,
Under expression --> Background color write this script
if(Credit <> Debit,red())
thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i think the above solution proposed by Elena paradoni would serve your purpose

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Elena for your reply. However, this highlights all the rows in my pivot table even where Credit = Debit for particular account.
Thanks,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear,
my solution works properly for table you displayed.... If your scenario is more complex, you should attach your qvw or your data.
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,

- « Previous Replies
-
- 1
- 2
- Next Replies »