Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

justin_morley
Contributor

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
kuba_michalik
Contributor III

Re: Re: Re: Conditional Formatting of a Dimension in a Pivot Table

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.

16 Replies
juleshartley
Valued Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

Hi,

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


ashwanin
Valued Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

Can you please try this

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

ashwanin
Valued Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

and it should be in

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

justin_morley
Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

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?

sundarakumar
Valued Contributor II

Re: Conditional Formatting of a Dimension in a Pivot Table

R u using any theme on ur app?

ashwanin
Valued Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

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

sundarakumar
Valued Contributor II

Re: Re: Conditional Formatting of a Dimension in a Pivot Table

PFA app, the conmdition is working fine,

Pease upload sample app for clarification..

-Sundar

Re: Conditional Formatting of a Dimension in a Pivot Table

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
justin_morley
Contributor

Re: Conditional Formatting of a Dimension in a Pivot Table

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.

Community Browser