topic Re: Dynamic Bar color in QlikView

Dynamic Bar color

Thu, 07 May 2015 07:15:15 GMT

I have a sales table which has 2 columns of total amount and selling agent. I also have a diagram which shows agents and their sum of selling.

I want to put an expression in background color which:

if the agent has sold more than average of all agents, makes the bar green. Otherwise, make it red (Regardless of active filter).

=If(Avg({1}[Selling Price])>> [Selling Price], RGB(128,255,0), RGB(255,0,0))

my changes the color when I change the current selection (filter).

any suggestion?

Re: Dynamic Bar color

sunilkumarqv — Thu, 07 May 2015 07:19:53 GMT

@hi Sep

As my understanding

you point is -if the agent has sold more than average of all agents, makes the bar green. Otherwise, make it red (Regardless of active filter).

If(agent>Avg{1[Selling Price]},Green,red)

share sample data for required o/p

Re: Dynamic Bar color

Thu, 07 May 2015 07:46:59 GMT

thanks sunil

Exactly as you explained. e.g. I want to see if Martin has sold more than average of all agents change the bar to green otherwise red like brad. But as you can see my expression doesn't respond properly.

if(Avg({1}[Selling Price])>>[Selling Price], RGB(255,0,0), RGB(128,255,0))

Re: Dynamic Bar color

santhoo_san — Thu, 07 May 2015 08:11:50 GMT

you may have to use the calculation in reverse order ie., If selling price > avg {1} selling price, green(), red().

Re: Dynamic Bar color

mrossoit — Thu, 07 May 2015 08:18:40 GMT

if(Avg(TOTAL [Selling Price])>[Selling Price], RGB(255,0,0), RGB(128,255,0))

Re: Dynamic Bar color

Thu, 07 May 2015 08:42:09 GMT

Hey,

this is an example of how I did it then try to do like i did maybe it can work for you too

you can replace Sum(#) and 25000 with your expressions

Re: Dynamic Bar color

antoniotiman — Thu, 07 May 2015 09:04:35 GMT

Hi Sep,

You can Use RangeAvg() Function

If(RangeAvg(Top([Selling Price],1,NoOfRows())) > [Selling Price],Red(),Green())

or Sum([Selling Price]) if Your expression is Sum([Selling Price])

Regards,

Antonio

Re: Dynamic Bar color

Mon, 11 May 2015 07:19:03 GMT

The thing is I want the qlikview to calculate the average. Take a look at this formula:
If(Sum({1} [Selling Price]) > (Sum({1}[Selling Price])/Count({1}[Agent Name])), RGB(0,0,0), RGB(255,255,255))
what is it I'm missing?!

Re: Dynamic Bar color

Mon, 11 May 2015 07:19:57 GMT

Antonio Mancini it's not working. Can you please point out what is the problem with above expression.

Re: Dynamic Bar color

Mon, 11 May 2015 07:22:07 GMT

Hi. Nope I want to calculate average selling by agents, Like following expression:

If(Sum({1} [Selling Price]) > (Sum({1}[Selling Price])/Count({1}[Agent Name])), RGB(0,0,0), RGB(255,255,255))

Re: Dynamic Bar color

mrossoit — Mon, 11 May 2015 07:59:06 GMT

I'm sorry but I don't understand.

You told "I want to see if Martin has sold more than average of all agents change the bar to green otherwise red".

Check out my sample please.

Re: Dynamic Bar color

Mon, 11 May 2015 08:24:53 GMT

In order to calculate avg sales you have to use the total qualifier as mentioned by Massimiliano Rosso