Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
@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
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))
you may have to use the calculation in reverse order ie., If selling price > avg {1} selling price, green(), red().
if(Avg(TOTAL [Selling Price])>[Selling Price], RGB(255,0,0), RGB(128,255,0))
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
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
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?!
Antonio Mancini it's not working. Can you please point out what is the problem with above expression.
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))