Conditional colour on bar per customer

[Anonymous]

I am trying to create a horizontal bar chart showing green if the number is positive and red if it is negative using this expression:

if

(num(

  Sum ({$<[Ar Transaction Trn Year]={$(=Max([Ar Transaction Trn Year]))},

  [Ar Transaction Invoice Date] ={"<$(=addyears(max ({$<[Ar Transaction Trn Year]={$(=Max([Ar Transaction Trn Year]))}>}[Ar Transaction Invoice Date])))"} >} [Ar Transaction Net Sales Value])

,'£###,###,###.')

-

num(

  Sum ({$<[Ar Transaction Trn Year]={$(=Max([Ar Transaction Trn Year])-1)},

  [Ar Transaction Invoice Date] ={"<$(=addyears(max ({$<[Ar Transaction Trn Year]={$(=Max([Ar Transaction Trn Year]))}>}[Ar Transaction Invoice Date]),-1))"} >} [Ar Transaction Net Sales Value])

,'£###,###,###.')

> 0, RGB(102,204,102), RGB(255,0,0))

Currently this performs  the calculation using the complete customer base so I only see green because the overall quantity returned is positive. I want it to show green if positive and red if negative for each customer e.g:

Customer 1 = 100 = Green

Customer 2 = -50 = Red

I know it is possible because but I don't know whether I need to use aggregation in the calculation to achieve it or if there is a simpler way that I am missing.

Any help will be greatly appreciated.

[ecolomer]

You can put the colour in the expresion:

[Anonymous]

Thanks Enrique. I have the RGB colour in the expression but it is aggregating on the total for all customers. I need to find a way to look at each customer individually so that the bar colour is green if the value is > 0 and red if not.

[ecolomer]

You need a colour different for the total?

[Anonymous]

No I don't need a total bar I just need bars that look like the attached image with red for - and green for +

[ecolomer]

OK.

The expressions in the top are working good?

Canyou upload a example file in order to try with it?

[jyothish8807]

Hi Clive,

PFA

Regards

KC

[Anonymous]

Thanks Jyothish. I have added my chart and I still see only one colour. I'm sure that I'm doing something really stupid.

[ecolomer · Accepted solution]

See now, with colour as an option in the expresion (is another option), but the good response is from Jyothish

[its_anandrjs]

Hi,

In your chart expression click on the ( + ) sign and write the expression

if (sum(B)>0,RGB(102,204,102), RGB(255,0,0))

Regards

Anand