Need help with attribute chart expression (background color)

[#1]

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

[sujeetsingh]

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.

[#3]

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?

[sujeetsingh]

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

And post it to meee

[#5]

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

[#6]

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.

[jonathandienst · Accepted solution]

Hi

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

[#8]

Perfect.  Thanks!