topic Color Expressions not working in QV9? in QlikView

Color Expressions not working in QV9?

Sat, 19 Sep 2009 04:51:13 GMT

Hi all,

I'm trying to set a color expression for a scatter chart. Basically, I would like all those balls that indicate a value > 400 to be a different color.

I've changed all the colors to a calculated value, but it's not taking. Does anybody have any suggestions what I'm doing wrong?

Thanks!

Oliver

Color Expressions not working in QV9?

Anonymous — Sat, 19 Sep 2009 07:53:21 GMT

Oliver, I think that sum(Price) is evaluated over all selected data here, and always >400 because of that. Try to us aggr() on the chart dimension, whatever it is, and possibly on week. Can't tell for sure without seeing dimensions and expressions, and the data model.

Color Expressions not working in QV9?

kji — Sun, 20 Sep 2009 17:23:03 GMT

Everything you set on the colors tab isevaluated once for the whole chart, so can not be used to set color of individual datapoints, the Background color you can set for expressions is evaluated for each datapoint, however i dont believe this is implemented for scatterchart mostly because each datapoint drives from more than one expression so could be ambigous which color to use.

Color Expressions not working in QV9?

johnw — Tue, 22 Sep 2009 02:41:56 GMT

You could always overlay two charts, one for the points <= 400, one for the points > 400, but it's not really ideal - you can only mouse over and click on the top chart, and you have dual maintenance. Hopefully there's a better answer.

Color Expressions not working in QV9?

Tue, 22 Sep 2009 03:00:56 GMT

EDIT: Sorry, I was thinking Grid Chart. It doesn't look like it works on a scatter (except for in the legend).

I think you should be able to use the Background Color expression on the Expressions tab. Set the background color to the color expression you used above.

I just did a test and used:

if(Sum(Sales) > 50, RGB(255, 0, 0))

If the value is greater than 50, it is red, if not, it goes to the default color (from the Colors tab). If you use your expression, it would completely ignore the Color tab.

Color Expressions not working in QV9?

Tue, 22 Sep 2009 03:26:47 GMT

Thanks for your input! It looks like Johan may (unfortunately) correct - I've tried using aggr(). It worked in a regular pivot table, but when I change the chart to scatter, it doesn't. Looks like I'm SOL! This would be a really cool feature to implement, though.

Cheers,

Oliver

Color Expressions not working in QV9?

johnw — Tue, 22 Sep 2009 04:08:39 GMT

OK, I think I have it, though I DO agree that QlikView should be able to do this by merely setting the color expression.

Add a calculated second dimension like aggr(if(sum(Price)>400,'Price > 400','Price <= 400'),FirstDimension). As long as you have prices both over and less than 400, it will use the first color in the chart for > 400, and the second for <= 400. See attached.

Color Expressions not working in QV9?

Wed, 23 Sep 2009 09:41:57 GMT

Thanks, John - I'll try that! Like you, I couldn't let this go and went with your earlier suggestion, i.e. overlaying two graphs. Not bad, either, though a little hard to edit.

Cheers,

Oliver

Color Expressions not working in QV9?

Fri, 25 Sep 2009 20:29:02 GMT

John,
I tried out your document, and something odd happens - after I refresh the document, the colors revert to their default multitude, and the dimension label changes as well. Do you think this has to do with me using version 9.0?

Thanks again for putting your time into this!

Oliver

Color Expressions not working in QV9?

johnw — Fri, 25 Sep 2009 21:37:24 GMT

I was able to duplicate the problem in 8.5 by hitting refresh a bunch of times. What it looks like to me is that if ALL of the data points have the same value for our calculated dimension, then similar to drill down groups, it will simply ignore that dimension. I don't know if that will or will not be a problem in your real application, but it's definitely a potential problem when they start making selections. While I don't know, it sounds like the sort of default behavior that we wouldn't be able to fix.

(Edit: Verified same behavior in 9.0)

Color Expressions not working in QV9?

Fri, 16 Oct 2009 20:45:49 GMT

I have a stacked bar chart. This shows proportions against three values, 'Yes', 'No' or 'N/A' (field "Satisfaction"). These are also represented by -1, 0 and 1 (field "Satisfied").

To make sure, especially when drilling down, that colours are Green, Red and Yellow respectively, I am trying to use the Color Area Base Color Calculated feature from the Colors tab. Neither of my formulae work:

=if(Satisfied=-1,rgb(0,255,0),if(Satisfied=0,rgb(255,0,0),rgb(255,255,0)))
=if(Satisfaction='Yes',rgb(0,255,0),if(Satisfaction='No',rgb(255,0,0),rgb(255,255,0)))

When I apply either of these formulae to the background colour of the cells of a straight table object, they both work just fine.

Is this a bug in Version 9, or just my formulae?

Color Expressions not working in QV9?

Fri, 16 Oct 2009 21:14:07 GMT

It would appear that whatever variables I am using for my colour formulae, they are null. Is there a way to perpetuate a value in a chart without it appearing in the chart? Seems to make the calculated colour pretty redundant if not.