Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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.

View solution in original post

6 Replies
Highlighted
rahulpawarb
Valued Contributor III

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

Highlighted
MK9885
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.

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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.

Highlighted
Partner
Partner

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.

View solution in original post

Highlighted
Partner
Partner

Re: Color Bars if Greater than average.

Oh, thank you so much, that worked!