Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Conditional Formatting of a Dimension in a Pivot Table

Hi,

I need to conditionally format a dimension in a Privot Table, could somebody point me in the right direction?

My data looks very approximately like this:

dimensionflagexpression
1087
2156
3012.1

I require conditional formatting to be placed on the dimension so that 1 and 3 are black and 2 is light grey, because of the value in the flag.

What I've tried so far:

1) Chart Properties>Dimensions>Used Dimensions -> expand the dimension and create the following expression under Text Color

=if(flag=1,LightGray(),Black())

This is completely ignored, as is =LightGray() or just Gray()

Why is this option even there if it doesn't work? Is there a switch somewhere I'm missing

2) Turn On Design Grid>Right Click in Cell>Select Custom Format Cell>Select Text Color>Base Color>Calculated and insert the same expression:

=if(flag=1,LightGray(),Black())

This time LightGray() on its own works, but the conditional expression does not.

Anyone know how I can acheive the conditional formatting I need? Looking through the archives I haven't found a satisfactory answer.

I'm on 11.20 SR5

Thanks,

Justin

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

If flag is associated with [dimension], but not necessarily with every combination of [dimension] and other pivot dimensions, then 'flag' in conditional expression could still evaluate to null and cause the test to fail - expression technically works,but you get the 0-flag color anyway. This is a part where the Only(TOTAL<dimension> flag) bit actually comes helpful. I'm attaching an example, hope it helps.

View solution in original post

16 Replies
Highlighted
Specialist
Specialist

Hi,

This should work. Have you tested that your condition is working correctly outside of the formatting expression?


Highlighted
Specialist
Specialist

Can you please try this

=if(Dimension=2,LightGray(),Black())

Highlighted
Specialist
Specialist

and it should be in

Chart Properties>Dimensions>Used Dimensions -> expand the dimension and create the following expression under Text colour

Highlighted
Creator
Creator

Hi Julian and Ashwani thanks for the reply.

- The expression I'm using

=if(flag=1,LightGray(),Black())

works if I place it in an expression rather than a dimension on the same pivot table. When I place it in a dimension, it is completely ignored, and the text is always black regardless of what is under Text Colour (if I set this to =Blue() or just Blue(), the text is still black)

Any ideas why this might be ignored? Am I missing a setting somewhere?

Highlighted
Specialist II
Specialist II

R u using any theme on ur app?

Highlighted
Specialist
Specialist

Hi Can you upload the document , bz these conditions are working fine here.

Highlighted
Specialist II
Specialist II

PFA app, the conmdition is working fine,

Pease upload sample app for clarification..

-Sundar

Highlighted
MVP & Luminary
MVP & Luminary

Perhaps your first dimension has several possible flag values. See attached example. When year 2009 is not selected the text color of the Product dimension is not changed. When year 2009 is selected there is only one year value per product and the text color of the products is changed.


talk is cheap, supply exceeds demand
Highlighted
Creator
Creator

Hi,

Thanks for all your replies.

Sundarakumar: No there is no theme

Gysbert: There are only two possible flag values, 1 and 0.

Ashwani: I can't send an example I'm afraid as our data and implementation is very sensitive.

Something else weird I've just noticed, it depends very much on the selection. Say I take the conditionality out of the expression against the colour of the dimension altogether. Text Color is now set to LightGray()

dimensionflagexpression
1087
2156
3012.1
40...
50...
60...

In my grid, 5 and 6 are now light gray and the rest are black, I make a different selection, all are black, another selection, every other cell is light gray. There doesn't seem to be any pattern to whether the formatting is applied or not.

Have I uncovered a bug in the pivot table? My pivot table is vastly more complex than stated, I just wanted to convey the basic problem.