Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Hide column in pivot table

Hi

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

informe.PNG

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?

ASD.PNG

Thanks in advance,

Cheers

6 Replies
YoussefBelloum
Champion
Champion

Hi,

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

Anonymous
Not applicable
Author

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!

isingh30
Specialist
Specialist

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

Anonymous
Not applicable
Author

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

awhitfield
Partner - Champion
Partner - Champion

HI,

how do you specify which column is hidden?

Andy

dzmitry_shmurye
Partner - Creator
Partner - Creator

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.