Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can one set the color of individual bars in a mini chart?

I have a table with the dimension "E-commerce sales rep", and an expression for "Final attainment this month", which is displayed as a mini chart over the dimension "Attainment year and month".  So far, so groovy:

QlikView - Sparklines Over Pivot Table.png

What I'd like to do now is color the columns in the "Attainment" column green or red, depending on whether the rep met his or her goal (e.g. >= 100% attainment) for that month.  This would let me drop the "Hits and Misses" column, increasing information density.

However, I have not been able to craft the right expression, or perhaps I'm putting it in the wrong place.  Under Mini Chart Settings | Color | Base Color | Calculated, I started with this expression:

=If(Avg([Final attainment this month]) >= 1.0, RGB(165, 207, 75), RGB(207, 75, 75))

This ought to give green for attainments of 100% or higher, and red for all others.  Instead, I get red across the board, which makes sense only in that the overall average, across all reps and months, is 0.89.  Perhaps I need an Aggr() in here?  I tried these variants, with no change, still all red:

=If(Aggr(Avg([Final attainment this month]), [E-commerce sales rep]) >= 1.0, RGB(165, 207, 75), RGB(207, 75, 75))

=If(Aggr(Avg([Final attainment this month]), [E-commerce sales rep], [Attainment year and month]) >= 1.0, RGB(165, 207, 75), RGB(207, 75, 75))

=If(Aggr(Avg([Final attainment this month]), [Final attainment this month]) >= 1.0, RGB(165, 207, 75), RGB(207, 75, 75))

Some reps certainly have achieved 100%+ attainment, as you can see in the screen shot and the second mini chart.  Is the color expression being evaluated only once for the entire table, making it impossible to reflect specific dimensions?  That seems like an odd omission.  Some reps have an overall average, across all months, over 100%, and I'd expect QlikView to at least evaluate the expression separately for each mini chart, meaning for each sales rep in this context.

If it matters, there's only one record for each rep/month intersection, so the Avg() function is in practice doing nothing.  I include it to allow for replacing the existing simple sales rep dimension with a sales team dimension, or examining attainment over years rather than months, if I choose to do so in the future.

Any ideas?  Has anyone tried to do this and either succeed or established it as impossible?  Thank you!

Jonathan Shaltz

1 Solution

Accepted Solutions
Gysbert_Wassenaar

No, that's not possible.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
shree909
Partner - Specialist II
Partner - Specialist II

Hi

can u post the sample file. that will be helpful..

Not applicable
Author

It's an abstract question: is it possible to set the bar color for each bar in a mini chart?

Gysbert_Wassenaar

No, that's not possible.


talk is cheap, supply exceeds demand
Not applicable
Author

Sadness.  Thanks for the answer!