10 Replies Latest reply: Jan 25, 2016 12:04 PM by Sangram Reddy

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

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

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

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

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

Expand Expression - Select Background Color - put condition like

if (Sales Variance < 0, redcolor())

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

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

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

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

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

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

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

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