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: 
posywang
Creator
Creator

background color of a dimension

Hi,

I have this issue to show background color of a dimension based off value in another dimension. I have attached a sample file and hope it helps to explain my issue. The issue is that since I don't have rating in August for one project, so even it is a business only project (with value of 1 in Business column), the background color of Project doesn't get picked up and there's no highlight. Can anyone help on this case? Please feel free to ask if my statement above is not clear enough. Thank you in advance!

20 Replies
vishsaggi
Champion III
Champion III

Can you try this in your project dimension background expr:

=IF(Business <> 0, LightGray(), White())

posywang
Creator
Creator
Author

Technically it works but it should only highlight Business only project, so if there's a IT project that doesn't have a rating in most recent month, August in this case, it will be falsely highlighted, using the Business<>0 solution.

posywang
Creator
Creator
Author

I uploaded a v2 document and your solution somehow not working in that case..

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

It's a tricky one because QV seems to want to do Dimension background coloring based based on the "first" cell in the row.  Because A / August is actually a missing (as opposed to null) I can't figure out an expression to make it work. Odd problem. I would think this would work but it doesn't, it's like it doesn't run the logic at all.

=if(Max(TOTAL<Project> Business)=1,LightGray(),White())

as a workaround you could make a dummy extra expression like "=1" and hide the display.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

vishsaggi
Champion III
Champion III

Where this IT project came from? Can you reconstruct your qvw file with relevant issues and attach here creating your scenario on what you expected output should be. ?

posywang
Creator
Creator
Author

Business = 1 is Business only project, and Business = 0 is IT project... Sorry about the confusion.

vishsaggi
Champion III
Champion III

Oh ok. Did you try what Rob suggested? Also try this:

=if(Business = 1,LightGray(),

  if(Business = 0,White(), LightGray()))

posywang
Creator
Creator
Author

Can you please explain how to hide a display once I create a dummy expression? Looks like that I can disable but not sure how to hide. Thanks!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

With a straight table, you can hide a column form the presentation tab.  For a pivot table, it's a little more complicated. To hide an expression column you need to make it zero width using a one-time macro.  You'll need to know the chart id from the properties general tab and which expression column you want to change (counting from zero) so #1 in your case for the second expression.  Paste this macro code in the module editor (Ctrl-m) and press the Test button the in the editor. That's it.

Sub SetCols

SetColWidthZero "CH01"

End Sub

REM You can't set a pivot table column to zero width from the UI. Use this macro to force the widths.

REM It only needs to be run once, not onOpen

Sub SetColWidthZero (id)

    set chart = ActiveDocument.GetSheetObject(id)

set cp = chart.GetProperties

set exprs = cp.Expressions

' First expression is 0, second is 1, ...

' Set the expression# to change

Set expr = exprs.Item(1).Item(0).Data.ExpressionVisual

expr.ColWidth = 0

chart.SetProperties cp

End Sub

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

rubenmarin

Another workaround can be adding a totals column at start, PFA.

Version (2) is using an empty TOTAL.