topic Re: Change the Color of Bar depending Upon a condition not on Value in QlikView

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

Wed, 21 Nov 2012 06:58:08 GMT

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

its_anandrjs — Wed, 21 Nov 2012 07:13:53 GMT

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

its_anandrjs — Wed, 21 Nov 2012 07:18:01 GMT

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

Wed, 21 Nov 2012 07:22:21 GMT

Hi Anand ,

Its not working here...

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

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

Wed, 21 Nov 2012 07:27:34 GMT

=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

Wed, 21 Nov 2012 07:40:58 GMT

Hai anand,

What u said That is Correct

Regards,

chandu

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

Wed, 21 Nov 2012 08:20:20 GMT

Hai,

Please see the Attachment here

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

Wed, 21 Nov 2012 08:50:33 GMT

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

its_anandrjs — Wed, 21 Nov 2012 09:17:28 GMT

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

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

Wed, 21 Nov 2012 11:16:58 GMT

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

its_anandrjs — Wed, 21 Nov 2012 11:24:57 GMT

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.

Let me know about this.

HTH

Regards,

Anand

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

Wed, 21 Nov 2012 11:31:11 GMT

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

its_anandrjs — Wed, 21 Nov 2012 11:36:34 GMT

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

Wed, 21 Nov 2012 11:40:04 GMT

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

its_anandrjs — Wed, 21 Nov 2012 11:47:00 GMT

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

Wed, 21 Nov 2012 11:50:33 GMT

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

its_anandrjs — Thu, 22 Nov 2012 09:11:56 GMT

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