topic Re: Dynamically change Bar graph shade based on Sales in QlikView

Dynamically change Bar graph shade based on Sales

raghavsurya — Mon, 25 Aug 2014 10:31:01 GMT

Dear All,

We are building Daily Sales analysis for our customer. We have a specific request wherein the customer wants colour shade to be changed/displayed based on Total sales amount. For Example if 1st of April has done maximum Sales, it should display in Dark Green and other day graphs should be shown in lighted shade in comparison with 1st April's sales.

Can anyone of you suggest how to achieve this requirement.

Regards,

Raghav

Re: Dynamically change Bar graph shade based on Sales

antoniotiman — Mon, 25 Aug 2014 10:41:37 GMT

Hi Raghav,

you can use the RangeSum() function in Background color of Text color of Expression,

like this

If(RangeSum(Top(Sum(sales),1,NoOfRows())) = Sum(sales),Green(),Red())

Regards,

Antonio

Re: Dynamically change Bar graph shade based on Sales

antoniotiman — Mon, 25 Aug 2014 11:12:41 GMT

Sorry,

RangeMax(............ (Not RangeSum)

Re: Dynamically change Bar graph shade based on Sales

raghavsurya — Mon, 25 Aug 2014 11:16:23 GMT

Thanks Antonio. I will work as per suggestion.

Re: Dynamically change Bar graph shade based on Sales

qlik_venu — Mon, 25 Aug 2014 12:25:36 GMT

You can also try this in the background color.

ARGB(

(

//this will be used for the alpha value of the function.

255*Aggr(Rank(Aggr(Sum(Sales), Country)), Country))

count(total Aggr(Sum(Sales), Country)),

17,184,75)

Re: Dynamically change Bar graph shade based on Sales

raghavsurya — Mon, 25 Aug 2014 12:26:20 GMT

Hi Antonio,

I used your suggested formula and getting only partial result. Your expression is marking one of the bar (lowest Sales) to red wherein other bars are having Green.

I need the lowest to have light green and largest in Dark green. The other bars should be between dark and light green (grandiant colors).

Regards,

Raghav

Re: Dynamically change Bar graph shade based on Sales

antoniotiman — Mon, 25 Aug 2014 12:55:09 GMT

Try Like This :

If(RangeMax(Top(Sum(sales),1,NoOfRows())) = Sum(sales),RGB(0,128,0),If(RangeMin(Top(SumSales),1,NoOfRows()))=Sum(sales),RGB(0,255,0),RGB(0,function(x),0)))

function x is proportional to sum(sales)

You can use RGB or ARGB as you prefer

Re: Dynamically change Bar graph shade based on Sales

Anonymous — Mon, 25 Aug 2014 13:07:13 GMT

You may wish to have a look at the ColorMix functions, they are described in the QlikView Desktop Help, and there is a Wizard to guide you.