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

Conditional Formatting of a Dimension in a Pivot Table

justin_morley — Tue, 04 Feb 2014 10:33:36 GMT

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:

dimension	flag	expression
1	0	87
2	1	56
3	0	12.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

Re: Conditional Formatting of a Dimension in a Pivot Table

juleshartley — Tue, 04 Feb 2014 10:41:44 GMT

Hi,

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

Re: Conditional Formatting of a Dimension in a Pivot Table

ashwanin — Tue, 04 Feb 2014 10:46:41 GMT

Can you please try this

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

Re: Conditional Formatting of a Dimension in a Pivot Table

ashwanin — Tue, 04 Feb 2014 10:48:25 GMT

and it should be in

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

Re: Conditional Formatting of a Dimension in a Pivot Table

justin_morley — Tue, 04 Feb 2014 11:05:32 GMT

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?

Re: Conditional Formatting of a Dimension in a Pivot Table

sundarakumar — Tue, 04 Feb 2014 11:21:43 GMT

R u using any theme on ur app?

Re: Conditional Formatting of a Dimension in a Pivot Table

ashwanin — Tue, 04 Feb 2014 11:22:46 GMT

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

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

sundarakumar — Tue, 04 Feb 2014 11:25:51 GMT

PFA app, the conmdition is working fine,

Pease upload sample app for clarification..

-Sundar

Re: Conditional Formatting of a Dimension in a Pivot Table

Gysbert_Wassenaar — Tue, 04 Feb 2014 11:27:22 GMT

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.

Re: Conditional Formatting of a Dimension in a Pivot Table

justin_morley — Tue, 04 Feb 2014 11:44:26 GMT

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()

dimension	flag	expression
1	0	87
2	1	56
3	0	12.1
4	0	...
5	0	...
6	0	...

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.

Re: Conditional Formatting of a Dimension in a Pivot Table

justin_morley — Tue, 04 Feb 2014 12:17:41 GMT

I've got it!

The answer is it only applies the formatting if there is something in the first pivoted field. there is a great deal of white space in my pivot table (which is desirable and unavoidable ) and it looks more like this:

dimension	flag	pivot value 1	pivot value 2
1	0
2	1		12.3
3	0
4	0
5	0	87	8
6	0	56

The first pivot value only has data against dimensions 5 and 6, and thus the formatting is only applied to dimensions 5 and 6.

However, I've failed to recreate these conditions in a stand alone app, so there must be something else going on here. Does this new information jog anybody's mind?

Re: Conditional Formatting of a Dimension in a Pivot Table

kuba_michalik — Tue, 04 Feb 2014 13:01:50 GMT

Dammit, and here I was just about to post the solution

However, a part of it still might be useful to you. I have no idea if it is really true, but it appears as if the conditional formatting expressions for dimensions are evaluated as if they were put on the first column of the chart. If that first column would happen to contain a missing combination of dimensions, tough luck Even an unconditional expression would get ignored in such case.

Hovewer, in this context, this:

flag=1

is for practical purposes shorthand for this:

Only(flag)=1

and that fact gives us a bit more control.

I assume you want to consider value of flag as in relation to the value of dimension only, so this:

If(Only(TOTAL<dimension> flag)=1,LightGray(),Black())

could be just what you are looking for.

Edit: it also needs to have "Populate missing cells" to work. If first expression column contains actual missing values, nothing can help. I attached a small example.

Edit2: It seems I overcomplicated this. Looks like "Populate missing cells" is all that is needed.

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

justin_morley — Tue, 04 Feb 2014 16:32:41 GMT

Great stuff! Thanks so much for this analysis and test app. Getting there by degrees!

Setting "Populate Missing" allows an unconditional expression on a dimension to work for me

However the conditional bit still fails to evaluate for some reason. Any ideas? I now have a reduced app which exhibits this behaviour but I'm reluctant to attach it due to data sensitivity. I may have a go at obfuscating the data and posting the app.

Thanks for your help,

Justin

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

kuba_michalik — Wed, 05 Feb 2014 08:34:17 GMT

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.

Re: Conditional Formatting of a Dimension in a Pivot Table

justin_morley — Wed, 05 Feb 2014 10:32:18 GMT

Bingo! You are a very clever man my friend!

The combination of "Populate missing cells" and your Only(TOTAL<dimension> flag) wizardry works a treat.

Now to spend a few hours trying to get my head around exactly what that expression is doing...

Thanks so much for your help.

Re: Conditional Formatting of a Dimension in a Pivot Table

anilet123 — Mon, 19 Mar 2018 10:19:29 GMT

Hi Jakub,

I tried the following expression in my PL Statement(straight table), it did not work for me when made selections on filters.

if(Only(TOTAL<[Exec P&L Heading]>[Exec P&L Calculation])='c' ,'<B>')

I had selected Account Number, the format bold disappeared for "TOTAL COST OF SALES " and " TOTAL OPERATING EXPENSES". Irrespective of the selections I would want the format to be unchanged.

Could you please help on this?

Regards,

Anilet Nirmal

Re: Conditional Formatting of a Dimension in a Pivot Table

anilet123 — Mon, 19 Mar 2018 10:42:50 GMT

Hi,

I got the solution. I applied the condition if(Only({<[Account Number]=>}[Exec P&L Calculation])='c' ,'<B>') and it works fine.

Thanks.

Regards,

Anilet Nirmal