topic Re: Hidden dimension in pivot table in QlikView

Hidden dimension in pivot table

Sat, 26 Oct 2013 08:03:27 GMT

Hi guys,

here is my pivot table. I have used few dimensions and i need to hide TxnLineId dimension but still want a view like this

RefNumber Rate

1 11

120

i.e. i want to maintain the granularity of TxnLineID even if it is hidden.

Can we still have the granularity of a dimension even if it is hidden?

Saurabh

Re: Hidden dimension in pivot table

Gysbert_Wassenaar — Sat, 26 Oct 2013 10:34:25 GMT

No, but you can set the label of the dimension to a space and set the text color of the values to 100% transparent and resize the column to minimal width. You'll need a macro if you want to set the width to zero.

It's also possible to create a calculated dimension for the Rates, but then you would not be able to have subtotals. Dimensions are not totalled.

Re: Hidden dimension in pivot table

Sat, 26 Oct 2013 15:14:45 GMT

Thanks for the solution!

Sorry for my ignorance but i could not find the macro. I did Ctrl+M but could not find any thing there.

Re: Hidden dimension in pivot table

Sat, 26 Oct 2013 15:28:11 GMT

I also found that if I add another dimension after TxnLIneId then the + and - sign shows up for TXnLIneId. I guess this can be fixed by setting the minimal width to zero.

Can you please give a hint that how that macro can be written and used.

Re: Re: Hidden dimension in pivot table

Gysbert_Wassenaar — Sun, 27 Oct 2013 14:46:57 GMT

Sub HideColumn

set chart = ActiveDocument.GetSheetObject("CH01")

set p = chart.GetProperties

' set the width of the 4th column to 0

chart.SetPixWidth 3,0

End sub

Re: Hidden dimension in pivot table

Sun, 27 Oct 2013 16:13:42 GMT

I added this macro as a function because my script was recognizing it as a macro. After adding it as function I am calling it as

Let v = HideColumn

but I saw that nothing happened then i added a MsgBox like

MsgBox "Got here" to see if MsgBox gets executed and it didn't so somehow my script is not calling this function...

My Macro security level is "safe Mode" and current local security is "only safe mode"