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

[rcorcoran]

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.

[settu_periasamy]

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]

Expression label

Rhona

[settu_periasamy]

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

[jonathandienst]

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.

[rcorcoran]

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))))

[settu_periasamy]

Hi,

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

Did you try the jontydkpi‌ Suggestion?