Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

melissapluke
New Contributor

Color Bars if Greater than average.

Screen Shot 2017-02-05 at 10.15.10 PM.png

Hi all, I have a graph that has an average revenue per month.  I want the bars to change color (from grey to purple) if the bar is greater than the average.  That average will change each month, so I would like it to be able to automatically update.

I tried if(sum(Revenue)>avg(Revenue),rgb(115,71,156),rgb(165,175,182)) but that doesn't seem to work.  I can only get it to work if I hardcode the average number in there.  like this: if(sum(Revenue)>116,rgb(115,71,156),rgb(165,175,182))

Any ideas? Thanks!

Melissa

1 Solution

Accepted Solutions
shubham_singh
Contributor II

Re: Color Bars if Greater than average.

try this

if(sum(Revenue)>avg(TOTAL Revenue),rgb(115,71,156),rgb(165,175,182)),

Looks like you want to compare Sum to overall average, TOTAL will ignore dimension and will give you overall average.

6 Replies
rahulpawarb
Valued Contributor II

Re: Color Bars if Greater than average.

Hello Melissa,

Trust that you are doing well!

The expression you have used looks correct to me. I request you to validate the sum(Revenue) & avg(Revenue) values against the dimension you used in the chart (to achieve the same you can add another expression avg(Revenue) on the bar chart). I strongly believe that the sum(Revenue) is always greater/lower than the avg(Revenue). Due to this you are not able to see the color difference in bar.

You can share the application with sample data, if problem still persists.

Hope this will help!

Regards!

Rahul

arvind654
Honored Contributor II

Re: Color Bars if Greater than average.

I'm not sure if this will work but if you can store your avg(Revenue) as a variable and use it to color code it?

Ex:

if(sum(Revenue)>$(vAvgRev),rgb(115,71,156),rgb(165,175,182))

or you can store both expressions into variable

if($(vSumAvg)>$(vAvgRev),rgb(115,71,156),rgb(165,175,182))


if doesn't work maybe can you share your sample data or app? I cannot find a scenario to replicate your issue.

melissapluke
New Contributor

Re: Color Bars if Greater than average.

Screen Shot 2017-02-06 at 9.03.08 AM.png

I turned the bar graph into a bar chart and the average revenue seems to calculate for each line and I want to compare it to the OVERALL average revenue ($116) and I cannot figure out how to keep it from taking the average for the single month, which since there is only one value for revenue per month, would always equal the sum.

melissapluke
New Contributor

Re: Color Bars if Greater than average.

  Hi, I am using QS Server, and do not know how to get the application file from there fore you.  Attached is the data I was using.  It was a very small set of data.  I was trying to test on this before putting this into a real life situation.  Thank you for your help.

shubham_singh
Contributor II

Re: Color Bars if Greater than average.

try this

if(sum(Revenue)>avg(TOTAL Revenue),rgb(115,71,156),rgb(165,175,182)),

Looks like you want to compare Sum to overall average, TOTAL will ignore dimension and will give you overall average.

melissapluke
New Contributor

Re: Color Bars if Greater than average.

Oh, thank you so much, that worked!