topic Re: Conditional colour on bar per customer in QlikView

Conditional colour on bar per customer

Anonymous — Sun, 02 Nov 2014 10:55:06 GMT

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:

(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.

Re: Conditional colour on bar per customer

ecolomer — Sun, 02 Nov 2014 16:35:58 GMT

You can put the colour in the expresion:

Re: Conditional colour on bar per customer

Anonymous — Sun, 02 Nov 2014 16:50:31 GMT

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.

Re: Conditional colour on bar per customer

ecolomer — Sun, 02 Nov 2014 17:02:28 GMT

You need a colour different for the total?

Re: Conditional colour on bar per customer

Anonymous — Sun, 02 Nov 2014 17:18:14 GMT

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

Re: Conditional colour on bar per customer

ecolomer — Sun, 02 Nov 2014 17:44:05 GMT

OK.

The expressions in the top are working good?

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

Re: Re: Conditional colour on bar per customer

jyothish8807 — Sun, 02 Nov 2014 17:57:18 GMT

Hi Clive,

PFA

Regards

Re: Re: Re: Conditional colour on bar per customer

Anonymous — Sun, 02 Nov 2014 19:17:56 GMT

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

Re: Re: Re: Re: Conditional colour on bar per customer

ecolomer — Sun, 02 Nov 2014 19:33:46 GMT

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

Re: Re: Re: Re: Conditional colour on bar per customer

its_anandrjs — Sun, 02 Nov 2014 19:45:49 GMT

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

Re: Re: Re: Conditional colour on bar per customer

Anonymous — Sun, 02 Nov 2014 21:50:33 GMT

Wow it was as easy as just changing the background text setting on the expression. I was trying to make it much more complicated. Thanks guys for your help.