Expression based on a 2nd IF Color condition data problem

[simongoodman]

I have one block of data with various format styles (e.g 0, 0.0, 0.0%) so I have used IF to build the expression in a pivot table. I also want to add  an IF colour condition to Background Color but it is based on a second block of data.

I can get it to work if the expression is a simple Sum(Data) expression but not when I apply IF to apply formatting. Are there any ideas how to solve this problem?

[Gysbert_Wassenaar]

See here and here

[simongoodman]

Sorry here is a qvw example of my problem. I hope someone can help me.

Simon

[Gysbert_Wassenaar]

See attached qvw. If that's not what you want please read this document (again) because then I have no idea what you want.

[simongoodman]

Hi Gysbert

I am trying to find a way to apply the IF color expression found in the second table's (CS Points expression and Background Color) against the expression in the first table (If Expression and If Color).

So in table 2, the CS Field 'AA' for Oct-11 returns a zero and so the IF condition returns red using [CS Points] data set I.E  = IF ([CS Points]=0, RGB (255,0,0), RGB (128,255,0))

In table 1 the [CS Data] data set for 'AA' in Oct-11 returns 10. But If want the color to reflect the above IF Color condition and return red.

Again sorry for any confusion. Thanks for taking time with this problem.

[Gysbert_Wassenaar]

That's not going to work. There are no records where [CS Points] has a value if CS Group is '% and Numbers'. So the color expression will always evaluate as false.

There is one thing you could try and that is putting two pivot tables on top of each other. See attached example.

[simongoodman]

Thanks that is very good suggestion. I will have a look at it. I have number of these pivots which I filtering on using a hide/show variable.

[simongoodman]

Hi Gysbert I have been playing with this and have some questions.

With table that holds the color statements and sits below the [CS Data] table how did you get [Custom] as a selection in the Current Style (style tab) as it hides the Dimension field name?

At the moment this is a test for a single pivot table. There will be a number of pivot tables and each may have different IF Color statements and they may change in the future. I thought of using variables but I can not get it work in Background Color and Text Color. Is it QV no no or is it a user error?

Once I created the variable  vCSColor, tried to replace =if ([CS Points]= 0 , RGB (255,0,0), RGB (128, 255.0)) with $(vCSColor).

Thanks

Simon

[Gysbert_Wassenaar · Accepted solution]

The 'Custom' style is because I used the Custom Format Cell option to hide the dimension headers in the color pivot table. You can enable that option by checking Always Show Design Menu Items on the Design tab of the User Preferences window. After enabling it you can right click a cell and choose the Custom Format Cell option.

The variable probably doesn't work because it starts with an = sign. In that case it is evaluated immediately whenever something in the document changes, for example a selection. If you want the variable to be evaluated only in the context in which it is used then it shouldn't include the = sign at the start.

[simongoodman]

Thanks bunches. Both problems are solved now. I read about both a while ago but my memory sucks.