Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Need help with attribute chart expression (background color)

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
Highlighted
Master III
Master III

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

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.

Highlighted
Not applicable

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

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?

Highlighted
Master III
Master III

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

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

And post it to meee


Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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.

Highlighted
MVP
MVP

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
Not applicable

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

Perfect.  Thanks!