topic Re: Need help with attribute chart expression (background color) in QlikView

Need help with attribute chart expression (background color)

Tue, 11 Dec 2012 02:03:22 GMT

I would like to set background color in attribute chart expression of a bar chart so that bars where the expression value is less than the average monthly sales appear in red and the others appear in green. If I arbitrarily hard code a threshold value like this it works:

if (sum(Sales)< 50000,red(),green())

But this does not:

if (sum(Sales)< (sum(Sales)/ count(DISTINCT MonthYear)),red(),green())

Re: Need help with attribute chart expression (background color)

sujeetsingh — Tue, 11 Dec 2012 04:54:58 GMT

Hi Aea,

Sorry to say but you should take hard coated in this case as

count(distinct monthyear)=1 always,

and hence your condition is always equal

Sum(sales)=sum)sales)/count(distinct monthyear)

and hence coloring is not applied.

Re: Need help with attribute chart expression (background color)

Tue, 11 Dec 2012 11:12:39 GMT

Son of Sardar - I don't understand. If I put =count (distinct monthyear) in a Text Object, I see 45, not 1. Can you clarify please?

Re: Need help with attribute chart expression (background color)

sujeetsingh — Tue, 11 Dec 2012 11:17:52 GMT

Ok just put another expression in your chart as count(distinct monthyear)

And post it to meee

Re: Need help with attribute chart expression (background color)

Tue, 11 Dec 2012 11:22:40 GMT

try something like this:

if(rangemin(.5,rangemax(condition),-.5)) < 0,

-Sqrt(-(rangemin(.5,rangemax(condition),-.5)))/(-.5)),

Sqrt((rangemin(.5,rangemax(condition),-.5)))/(.5))),

ARGB(255, 255, 0, 0), ARGB(255, 0, 255, 0), ARGB(255, 255, 255, 255))

Re: Need help with attribute chart expression (background color)

Wed, 12 Dec 2012 14:28:47 GMT

OK I see what you mean. If I put = count(distinct monthyear) in a text box, I get 45 because there's no dimensional split, but in my bar chart I get 1 as you said because it's dividing the 45 by the # of dimension values in the chart - so 45 / 45 = 1.

Thanks.

Re: Need help with attribute chart expression (background color)

jonathandienst — Wed, 12 Dec 2012 14:43:08 GMT

Not necessary to hard code - just add a TOTAL modifier to the sum and count, like this

sum(Total Sales) / count(DISTINCT Total MonthYear)

The total causes the dimensions to be ignored, so it will return the same value as the text box.

Regards

Jonathan

Re: Need help with attribute chart expression (background color)

Wed, 12 Dec 2012 14:52:03 GMT

Perfect. Thanks!