Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic Bar color

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?

11 Replies
sunilkumarqv
Specialist II
Specialist II

@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

Not applicable
Author

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))

Capture.PNG

santhoo_san
Partner - Creator II
Partner - Creator II

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

mrossoit
Creator II
Creator II

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

Not applicable
Author

Hey,

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

Capture.PNG

Capture1.PNG

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

antoniotiman
Master III
Master III

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

Not applicable
Author

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?!

Not applicable
Author

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

Not applicable
Author

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))