topic Re: Hiding Dimension (pivot) by using Sheet Properties in QlikView

Hiding Dimension (pivot) by using Sheet Properties

Wed, 06 Apr 2011 14:47:24 GMT

I'm trying to hide a column by setting a 'Sheet Object Event trigger - OnActive' action from the Sheet Properties, Triggers tab - I don't seem to be able to get the VB to work.

Sub HideDim

set obj = ActiveDocument.GetSheetObject("CH364")

set objProp = obj.GetProperties

set objDim = objProp.Dimensions

objDim(2).ColWidth = 0

obj.SetProperties objProp

end sub

Can someone point out my obvious mistake as I'm not a VB developer

Hiding Dimension (pivot) by using Sheet Properties

Anonymous — Wed, 06 Apr 2011 14:53:59 GMT

Chris,

This is what I use to hide column in a pivot table (in this example chart CH01, column 2):


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

Hiding Dimension (pivot) by using Sheet Properties

Wed, 06 Apr 2011 15:01:43 GMT

Another option is to disable columns:

set chart = ActiveDocument.GetSheetObject("CH678")

cp = chart.GetProperties

set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData 'where the first item(0) is item(column number)

expr.Enable = false

chart.SetProperties cp

Hiding Dimension (pivot) by using Sheet Properties

Wed, 06 Apr 2011 15:42:18 GMT

I can't get this to disable a dimension colunm it stops at

set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData 'where the first item(0) is item(column number)

Any ideas?

Hiding Dimension (pivot) by using Sheet Properties

Wed, 06 Apr 2011 16:13:56 GMT

Sorry i thought you wanted to hide an expression. You should either use Michael's solution above or create a calculated dimension.

To create a calculated dimension take a look at the following post, there's a sample file called test.qvw that explains what to do:

http://community.qlik.com/forums/t/19245.aspx

Basically you will create a dimension that looks like:

=if($(dimShow)=1,'Dim2',' ')

The dimShow will be set in the maco to be either 1 or 0.

If you want you can post your qvw I could help set it up.

Hiding Dimension (pivot) by using Sheet Properties

Thu, 02 Jun 2011 02:51:41 GMT

Otherwise you can have a look at this topic as well:

http://community.qlik.com/message/37998#37998

Re: Hiding Dimension (pivot) by using Sheet Properties

Anonymous — Thu, 13 Aug 2015 15:16:01 GMT

Hi I am trying to use this code to hide a colunm in a pivot table but without success, Can you please help in getting the code right please My sheet object Id is SH04 coloumn is 11 and chart Id is CH06 Below is the senario why I want to hide the column

am working on a financial dashboard where in it has Group Name, Account,Actual ,Budget ,variance column for Period Year to Date and Full year respectively, Now as the data is not available still for Full year I want to Hide Actual Column for Full Year, When am trying to hide Actual by writing false() or 0 in Condition expression it is hiding the entire Actual column ie also for Period & Year to Date, I just want to Hide Actual Column for Full Year, Is this possible,

I searched on line for this and found some macro to hide the column by adjusting width to 0 but couldn't able to understand where to put that code or how to use

Thanks a lot fro your help

Re: Hiding Dimension (pivot) by using Sheet Properties

Tue, 18 Aug 2015 22:16:43 GMT

I'm using this code to hide a column, but I'd also like to have some code that "undoes" the hiding, so that I can toggle back and forth. How do I "unhide" the column I hid?