Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Change bar color is it more than average value.

Hello,

I need an expression to show bar in different color if it is more than average value. the bar chat is showing average value by each month.

I'm trying with below expression but not working

if((Avg(Aggr(Sum({<Product ={Apple*}>}Sales),Month)))>(Avg(total{<Product ={Apple*}>}Sales))), '#FF0000','#17DD1D')

Average.PNG

Thanks in advance for your help

7 Replies
Highlighted
Partner
Partner

Hi,

 

You seem to be almost spot on.

Some of the below may sound redundant, but I am just listing them for the sake of a full reusable description:

1) Can you please try setting contents of a set expression surrounded by '

 

2) Can you please check if this is the exact expression behind the value of the bars:

Avg(total{<Product ={'Apple*'}>}Sales)

If not, make sure to replace that part with whatever is the exact expression you are using

3) Can you make sure that you are not using limits on your month dimension. This may cause visual discrepancies.

 

4) If the above don’t work, try this:

if($(=Avg(Aggr(Sum({<Product ={'Apple*'}>}Sales),Month)))>(Avg(total{<Product ={'Apple*'}>}Sales))), '#FF0000','#17DD1D')

Let me know if that help!

PS: I am on a phone so typos to the above are to be expected.

Kind regards,

S.T.

 

Highlighted
Contributor II
Contributor II

Sorry. this is not working. 

Highlighted
Partner
Partner

Hi,

 

could you please post the expressions you are using in the bar chart?

Also, you are adding the code in the appearance/colors section having the setting “expression is a colour code” on, right?

 

Thanks!

Highlighted
Contributor II
Contributor II

Hello,
Below is my chart measure
Avg({}Sales))
And I'm using this expression under color setting section only.
Thanks for your reply.
Highlighted
Contributor II
Contributor II

Sorry typo mistake. 

Below is correct expression

avg({<product={'apple*'}>}sales) 

Highlighted
Partner
Partner

Hi,

if($(=Avg(Aggr(Sum({<Product ={'Apple*'}>}Sales),Month)))>(Avg({<Product ={'Apple*'}>}Sales))), '#FF0000','#17DD1D')

Or this

if((Avg(Aggr(Sum({<Product ={'Apple*'}>}Sales),Month)))>(Avg({<Product ={'Apple*'}>}Sales))), '#FF0000','#17DD1D')

Small correction to the formula then.

Highlighted
Contributor II
Contributor II

Hello,
If you see my expression, I was also trying same. But this is not working
and throwing error as "error in expression: if take 2-3 parameters".