16 Replies Latest reply: Nov 22, 2012 4:11 AM by Anand Chouhan

# 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.

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Hi,

Use Script some thing like this

if( Sum(Sales)  >

Aggr ( Sum(Sales) /

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

Regards,

Anand

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Hi Anand ,

Its not working here...

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

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

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

=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....

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Hai anand,

What u said That is Correct

Regards,

chandu

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Hai,

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

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

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

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( ) )

HTH

Regards,

Anand

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

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.

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Hi,

It is not hard coded the formula in variable

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

is updated according to the values that are changes in your columns.

HTH

Regards,

Anand

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Ya Anand I got that it is not hard coded but i have tried this Variable concept also but it is also not working for me

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

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

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Finally i got it   .

Thanks Anand Chauhan for helping me out.

I want to know one thing more that what is the use of = operator Exactly out in it.

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

When ever you create any variable and the variable gets value from any formula if you not put = sign before formula like

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

it will treat like a text script and but if you put

=(Sum(Sales)/(Count(DISTINCT(month))))  = before that so by this the value of formula is assign to the variable and variable holds the value of formula that is the difference.

Regards,

Anand

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

Now i have a complete Picture of it.

Thanks Anand.Can you provide me your personal e-mail id .So i can remain in your touch always

• ###### Re: Change the Color of Bar depending Upon a condition not on Value

I am available every time in the Qlikview Community and if you want to ask a question then send directly a Private Discussion to me so i respond to you on your questions.

Regards,

Anand