4 Replies Latest reply: Feb 24, 2010 4:09 PM by hngo2000 RSS

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

    hngo2000

      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!

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

          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.

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

              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.

               

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

                  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.