Comparing two columns and show BG color

[#1]

Hi all,

Thanks for all your time for viewing this post.

I have searched through the forum but could not find a solution to my problem.

What I am trying to do is to compare two columns in a pivot table, and show red in bg if the value in the second column is greater than the first, and green if the second less than the first. I've tried to use both the column expression as well as the function column() to compare the two values. However, none of them returned the correct visual cues.

The bg color changed correctly for all the correct comparison. However, it also changed sometimes when the values are the same.

The expression I used:

if(avg({$<Revision={'70'}>}CPU)>avg({$<Revision={'60'}>}CPU), lightred(),

if(avg({$<Revision={'70'}>}CPU)<avg({$<Revision={'60'}>}CPU),green()))

and

if(column(2)>column(1), lightred(),

if(column(2)<column(1),green()))

in the definition of bg color.

Anyone has any idea what's wrong with this?

Thanks!

[MayilVahanan]

Hi

Try like this

In chart properites -> number tab, changed the fixed decimal point from 2 to 7(for ex), and you can able to find the difference between 2 columns. There might be a small difference.

In that case, you can use Round(), Ceil() or Floor() function based on your requirement.

hope it helps

[a_mullick]

Hi,

Are you rounding expressions to 2 dp? If you are then you may be obscuring the 'true' values of column 1 and oclumn 2 e.g. column 1 may be 16.68 and column 2 may be 16.66 - both round to 16.70, but column 2 is really less than column 1.

Azam

[#4]

‌Hi

thanks for your reply!

I Suspected what you said as well. However, the actual value for 16.80 is 16.8. It appears to be 16.80 because I set it to be fixed to 2 decimal point. So it is not because of rounding of numbers.

[#5]

‌Hi

thanks for your reply!

Please refer to my post above. Thanks!

[avinashelite]

Hi,

can you please share your app??

[MayilVahanan]

Hi

Please provide sample data

[vardhancse]

Hi Try,

=Expression_1>=After(Expression_2) or Expression_1>=Before(Expression_2),'▲','▼'))

[#9 · Accepted solution]

Hi all,

Thanks for all your help.

I have solved the problem. In my previous version, the column 60 and 70 are separate expressions I created using set Analysis. They are actually under one column called Revision in the table. Now I added in Revision as a dimension and move it to the row, and the color appears to be correct now. I am not sure how it is related.