Hi Julian and Ashwani thanks for the reply.
- The expression I'm using
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?
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.
comm106232.qvw 187.5 K
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.
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 dimension 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?
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:
is for practical purposes shorthand for this:
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:
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.
test.qvw 155.8 K
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,
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.
test2.qvw 151.0 K
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?