Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissapluke
Partner - Creator
Partner - Creator

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
Partner - Creator II
Partner - Creator II

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
rahulpawarb
Specialist III
Specialist III

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

MK9885
Master II
Master II

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

  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
Partner - Creator II
Partner - Creator II

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
Partner - Creator
Partner - Creator
Author

Oh, thank you so much, that worked!