How to use "visual cue" on non-numeric values?

[#1]

I am writing a time-entry report and wanted to highlight all users with <35hours/week yellow and all users with 0 hours/week RED. I got the yellow to work but I can't get the RED to work. In the pivot table, all 0-hours/week entries show up as "-" sign. I tried using =<'-' but that didn't work. Any suggestion as to how I can do this?

Thank you!

[#2]

Are those zero values null in your data? Are you using an aggregate function (sum, count, etc) in your expression?

You may need to use the Text Color expression on your expression. Under the Expressions tab, click the plus next to your expression. You will get more properties. Select Text Color and then Edit Expression. Then enter something to color the nulls. It may be:

If(Expression = Null(),RGB(255,0,0))

That will color the null values red, but also pass off any other values to follow your Visual Cues. If you add the false part of the expression, the Visual Cues will be ignored.

[#3]

I am using a SUM. The expression is "Sum(ProjectTime) + Sum(NonProjectTime)". For entries that does not have any entered time, Qlikview show it with a value of "-". Can't tell what this really mean. I tried the above suggestion but can't seem to make it work either.

[#4]

It seems like a Sum will usually return zero even if there are null values. Try using:

Sum(ProjectTime + NonProjectTime)

You could also try something like in a new expression:

if(Sum(ProjectTime + NonProjectTime)=Null(), 'Null',
if(Sum(ProjectTime + NonProjectTime)=0, 'Zero',
if(Sum(ProjectTime + NonProjectTime)>0, 'Greater')))

That could tell you what the value is coming out as. You can then use the answer there to create your Text Color expression.

[#5 · Accepted solution]

I finally got it to work. I basically did the following:

1) under the Presentation tab, set the "populating missing cells" and set the Null Symbol and MIssing Symbol to "0".

2) under the Visual Cues, set the color RED on "0"

Thank you.