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: 
Not applicable

Change the Color of Bar depending Upon a condition not on Value

I have a bar chart in which X-axis represent 12 months & on Y-Axis it is Showing Sum(Sales).I have made a

Reference Line by making use of --(Sum(Sales)/(Count(DISTINCT(month))))----Avererage Value.

I want to change the color of Bar in Bar chart depending upon the following condition:-

if(sum(Sales) >(Sum(Sales)/(Count(DISTINCT(month)))),Blue(),Red())

but it is not working...!

If i but a vlaue in place of Average Calculation then i am getting my Result.

if(sum(Sales) >65,Blue(),Red())

Find the attached Sample Qvd for this Problem.

1 Solution

Accepted Solutions
its_anandrjs

When writing the formula in variable write some like

Variable name vAvg and in definition write

=(Sum(Sales)/(Count(DISTINCT(month)))) with equal sign.

and use this variable in in Background properties

if( Sum(Sales)  >

    $(vAvg),Blue( ),Green( ) )

Regards,

Anand


View solution in original post

16 Replies
its_anandrjs

Hi,

Use Script some thing like this

if( Sum(Sales)  >

    Aggr ( Sum(Sales) /

    ( Count(DISTINCT(month) ) ),Year,Month ),Blue( ),Green( ))

Regards,

Anand

its_anandrjs

Hi,

You have to use some different condition because this condition

Sum(Sales)  >

    ( Sum(Sales) /

    ( Count(DISTINCT(month) ) )

always true  so it prints 1 condition for chart

HTH

Regards,

Anand

Not applicable
Author

Hi Anand ,

Its not working here...

Download that sample Qvd attached & try ......

Not applicable
Author

=IF((Sales)>( Sum(Sales) /( Count(DISTINCT(month) ) )),Red(),Blue())  

condition is returing Blue color , So i think that it returns False vale not True value as you have said Above....

Not applicable
Author

Hai anand,

What u said That is Correct

Regards,

chandu

Not applicable
Author

Hai,

Please see the Attachment here

Not applicable
Author

Hi Ram,

I want to have a single chart only in which the color of the bar should get changed if it is higher then Average(Red)

and (Blue) with Lower value then Average.

You have created 2 chart and Over shadow One.

I want it to work Dynamically.

& also want to know that how can you justify Anand Answer without a proof...?

Thanks for helping me Out...

its_anandrjs

Hi,

As i understand you want to create a bar chart where your average value that is 65 which also a reference line and you want to display where a chart crosses a reference line so for this

create a variable

vAvg =(Sum(Sales)/(Count(DISTINCT(month))))

and on the Background color change use this variable in your script like

if( Sum(Sales)  >

    $(vAvg),Blue( ),Green( ) )

And then please check your chart colors

HTH

Regards,

Anand

Not applicable
Author

Hi Anand,

I have tried that also wat you have said.All the bars are of Green Color only  

My Average is not fixed to 65.It will change depending upon the number of month selected,that's why i am using

Expression not a hard coded Value-65.