topic Re: Expression based on a 2nd IF Color condition data problem in QlikView

Expression based on a 2nd IF Color condition data problem

simongoodman — Fri, 31 May 2013 09:27:48 GMT

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?

Re: Expression based on a 2nd IF Color condition data problem

Gysbert_Wassenaar — Fri, 31 May 2013 09:33:50 GMT

See here and here

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Fri, 31 May 2013 11:23:55 GMT

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

Simon

Re: Expression based on a 2nd IF Color condition data problem

Gysbert_Wassenaar — Fri, 31 May 2013 11:39:03 GMT

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

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Fri, 31 May 2013 12:34:12 GMT

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.

Re: Expression based on a 2nd IF Color condition data problem

Gysbert_Wassenaar — Fri, 31 May 2013 13:01:48 GMT

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.

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Sat, 01 Jun 2013 12:44:23 GMT

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.

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Mon, 03 Jun 2013 09:32:07 GMT

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

Re: Expression based on a 2nd IF Color condition data problem

Gysbert_Wassenaar — Mon, 03 Jun 2013 09:45:49 GMT

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.

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Mon, 03 Jun 2013 09:57:49 GMT

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

Re: Expression based on a 2nd IF Color condition data problem

simongoodman — Wed, 05 Jun 2013 09:08:17 GMT

Hi Gysbert

I wonder if I can press my luck and ask for your help again. Another issue has come up when applying this solution to multiple pivot tables.

I have used a variable and buttons to help the user selection process. When the saved qvw is reopened and you make a selecion the IF Color statement table which was on the bottom now appears on the top. Very odd.

The attached example qvw has three selections; Agency Relation, LCO and Customer Service. When saved on the Customer Service and reopened the other selections work. If you then save on the LCO selection and reopen the problem appears. Select Customer Service, resave and reopen presto all is good.

Re: Expression based on a 2nd IF Color condition data problem

Gysbert_Wassenaar — Wed, 05 Jun 2013 09:12:29 GMT

On the Layout tab of the pivot tables that should stay in the background set the layer option to Bottom. As long as the pivot tables with the values are placed in a higher layer they should be displayed on top of the background pivots.