How do I colour the background of negative Sales Variance numbers in a table

[#1]

Hello everybody,

I've been trying to colour the background of negative Sales Variance % in a table and it has worked in other tables before, but not this time. This is the table  where I want to colour the background red (Sales Variance%):

The expression for Sales Variance % looks like this:

Sum({$<Year={$(=Max(Year))}, Mon={$(=Month(Today()))}>}Sales)/

Sum({$<Year={$(=Max(Year)-1)}, Mon={$(=Month(Today()))}>}Sales) - 1

Can anyone tell me how I can write an expression that makes the colour in the background red?

Sincerely,

Betty Habtemariam

[tahirkhalil · Accepted solution]

Hi,

You can use expression background color for this.

In expression, if you click +, you can define background color. In your specific condition you can put:

if( Sum({$<Year={$(=Max(Year))}, Mon={$(=Month(Today()))}>}Sales)/

Sum({$<Year={$(=Max(Year)-1)}, Mon={$(=Month(Today()))}>}Sales) - 1 < 0, Yellow())

I skipped otherwise (False) condition of IF.

if( Sum({$<Year={$(=Max(Year))}, Mon={$(=Month(Today()))}>}Sales)/

Sum({$<Year={$(=Max(Year)-1)}, Mon={$(=Month(Today()))}>}Sales) - 1 < 0, Yellow(), Green())

Regards,

TK

[Chanty4u]

hi,

chk dis

Background colour using expression

[manojkulkarni]

Expand Expression - Select Background Color - put condition like

if (Sales Variance < 0, redcolor())

[Chanty4u]

How to create conditional background color?

[NareshGuntur]

You need to expand the Measure under Data-->Columns-->            and you'll find Background Color Expression like below

[#7]

Thank you! I managed to fix the background colour. However, now the problem is that everything is red, whether or not the numbers are positive or negative, haha! It's a neverending story Any thoughts on that?

What it looks like now:

The expression:

if (Sum({$<Year={$(=Max(Year))}, Min={$(=Month(Today()))}>}Sales) - 

Sum({$<Year={$(=Max(Year)-1)}, Min={$(=Month(Today()))}>}Sales)/

Sum({$<Year={$(=Max(Year)-1)}, Min={$(=mMonth(Today()))}>}Sales) < 0, RGB(255,0,0), red ())

Sincerely,

Betty Habtemariam

[reddy-s]

Hi Betty,

You are making use of the same color when its true as well as when its false.

RGB(255,0,0) is red

So change the colour codes:

Green would be RGB(146,208,80)

Amber would be RGB(255,192,0)

Thanks,

Sangram

[#9]

Thank you! 😃 It worked!

[#10]

Thank you! 😃  Sometimes I'm just too blind, haha! Sorry for the late reply. I was away from work a couple of days.

Sincerely,

Betty Habtemariam