Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
Hi,
try with =if(Dimensionality()=0.. it will calculates your expression only for the total line
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!
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
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
HI,
how do you specify which column is hidden?
Andy
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.