Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Expression based on a 2nd IF Color condition data problem

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?

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand

View solution in original post

11 Replies
Highlighted
MVP & Luminary
MVP & Luminary

See here and here


talk is cheap, supply exceeds demand
Highlighted
Creator
Creator

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

Simon

Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand
Highlighted
Creator
Creator

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.

Highlighted
MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand
Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

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

Highlighted
MVP & Luminary
MVP & Luminary

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.


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Creator
Creator

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