topic Hide column in pivot table in QlikView

Hide column in pivot table

Anonymous — Tue, 05 Jun 2018 14:48:22 GMT

We have a Pivot Table with 3 dimensions, one of them placed in the X axis (TYPE LICENSE), and 3 expressions (Cars, Drivers and %), two of them shared by all dimension values, and a third one "%" which we only want to show for ONE of the values (TOTAL).

We wonder how we can hide the % column for all values except "total" (please, consider it as a dimension value, not calculated by "show partial sum"). We have achieved to leave as NULL these columns, but not hide them.

The "%" expression is the following. :

=if(ColumnNo() = FieldIndex('COD_TYPE_LICENSE','TOTAL'),
SUM({$<COD_TYPE_LICENSE={1}>} CLIENTS)/(SUM({$<COD_TYPE_LICENSE={1}>} CLIENTS)+SUM({$<COD_TYPE_LICENSE={2}>} CLIENTS))
, null())

I'm trying to hide the columns using the CONDITIONAL option and expression "ColumnNo() <> FieldIndex('COD_TYPE_LICENSE','TOTAL')", but the function ColumnNo() is not returning any value when I use it in CONDITIONAL option (althought yes in metric expression).

Is there any way to hide an expression column for some dimension values?

Thanks in advance,

Cheers

Re: Hide column in pivot table

YoussefBelloum — Tue, 05 Jun 2018 14:57:16 GMT

Hi,

try with =if(Dimensionality()=0.. it will calculates your expression only for the total line

Re: Hide column in pivot table

Anonymous — Tue, 05 Jun 2018 14:59:14 GMT

Hi Youssef

Thanks for your suggestion!

It doesn't work. I forget to say that my "TOTAL" column is a Dimension value, it's not generated by "Show partial sum" of Pivot Table. For this reason, I can't use dimensionality, it's the same in all cases.

Cheers and thanks again!

Re: Hide column in pivot table

isingh30 — Tue, 05 Jun 2018 15:04:23 GMT

May be this - Pivot Column Width to Zero

Use the next macro to "hide" columns in pivot:

sub Squeeze
call HideColumn("CH01", 2)
end sub
'
private sub HideColumn(ch, n)
set ch = ActiveDocument.GetSheetObject(ch)
ch.SetPixWidth (n-1), 0
end sub

Re: Hide column in pivot table

Anonymous — Tue, 05 Jun 2018 15:15:57 GMT

Hi!

Thanks, but would like to export the pivot table to excel with unneccesary columns! with the macro we hide columns in QV, but when we export the table to excel, it keeps empty columns.

Cheers

Re: Hide column in pivot table

awhitfield — Wed, 24 Oct 2018 13:29:05 GMT

HI,

how do you specify which column is hidden?

Andy

Re: Hide column in pivot table

dzmitry_shmurye — Mon, 19 Nov 2018 13:12:02 GMT

Hi. How is it possible to call the macro by expanding or collapsing a pivot table?

I thinks we need to have an event to attach the macro.