Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

rcorcoran
Contributor

how to hide columns in pivot table but still use the expression in them for other columns

I have a pivot table sceenshot attached where I have added in columns at the end that I have used to calculate % of targets.  I use these columns in the background colour of the columns that  show red, amber, green.

my problem is that I need to hide the columns at the end showing the targets ie Target MH6 Column 5c 10% etc.

When I hide them using the conditional view =0 or =False() the background colour wont work for the other columns as the expressions seem to be hidden also.

Any help on how I can hide these last four columns and keep the colour coding.

6 Replies

Re: how to hide columns in pivot table but still use the expression in them for other columns

Hi,

Are you using the Column Number or Expression Label for Background Color?

Column Number won't work if you are using conditionally hide..

rcorcoran
Contributor

Re: how to hide columns in pivot table but still use the expression in them for other columns

Expression label

Rhona

Re: how to hide columns in pivot table but still use the expression in them for other columns

can you post the sample file or your expression for Background color?

MVP
MVP

Re: how to hide columns in pivot table but still use the expression in them for other columns

Its not possible to hide columns but keep their values in a pivot table. You can do that in a straight table with the hide column option in the presentation tab, but the pivot tables only have the conditional display option in the Expressions tab which hides the column and does not calculate the column.

If you make the columns as narrow as possible, with a space as a label and the text colour the same as the background colour, they will (almost) be hidden. You may want to turn off the vertical expression cell borders as well.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
rcorcoran
Contributor

Re: how to hide columns in pivot table but still use the expression in them for other columns

expression for background colour

if(num(Sum({$<[CAMHS]={7,8,9,10,17,18,19,20,27,28,29,30,37,38,39,40}>}ActualValue)/
(
Sum({$<[CAMHS]={7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46}>}ActualValue)), '##0%')>=[Target MH 6 for column 5c 5%],RGB(81,173,152),
if(num(Sum({$<
[CAMHS]={7,8,9,10,17,18,19,20,27,28,29,30,37,38,39,40}>}ActualValue)/
(Sum({$<
[CAMHS]={7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46}>}ActualValue)), '##0%')<[Target MH 6 for column 5c 10%],RGB(208,62,81),
if(num(Sum({$<
[CAMHS]={7,8,9,10,17,18,19,20,27,28,29,30,37,38,39,40}>}ActualValue)/
(Sum({$<
[CAMHS]={7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46}>}ActualValue)), '##0%')>=[Target MH 6 for column 5c 10%] and
num(Sum({$<
[CAMHS]={7,8,9,10,17,18,19,20,27,28,29,30,37,38,39,40}>}ActualValue)/
(Sum({$<
[CAMHS]={7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46}>}ActualValue)), '##0%')<[Target MH 6 for column 5c 5%],RGB(233,174,17))))

Re: how to hide columns in pivot table but still use the expression in them for other columns

Hi,

Really difficult to find. Is it possible to post the sample?

Did you try the jontydkpi‌ Suggestion?

Community Browser