Hide column in pivot table

[#1]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0243.Sample1.xls:550:0]In a Pivot table in the dimension there are tthree hierarchies ( say Field1 , Field2 and Field3 ). Field1 has sequence field ( say FieldSeq1) , similarly Field2 has sequence field (say FieldSeq2). We have to display the fileds sorted by first FieldSeq1 then FieldSeq2. But this sequence fields should not be displayed . Additionally Field3 should also not be displayed.

Till this point requirements matches with Straight tables. But we have some additional requirements where Straight table fails .

We need sub totals of two expresssion fields (exp1, exp2 say). Sub totals based on dimension Field3( though the column should be hidden), and same for Field1 and Field2. In the Sub Totals Row also we need Label with current Value of Field1, Field2 and Field3 respectively.

Attached is the format in which final report should be , but FieldSeq1, FieldSeq2 and Field3 columns should not be visible.

Please let me know if there is any solution like this.

[#2]

Hiding columns in Pivot is not possible! In the properties of a pivot there is Tab for sorting by expression (FieldSeq1 then FieldSeq2), so maybe you can do it without these columns?

[#3]

Hi,

Jochem is right.

You can try indent mode check box in style tab of pivot chart.

[#4]

It solved the problems partially . Now FieldSeq1 and FieldSeq2 need not be selected in used dimension. Thanks to Jochem.

But How to eliminate the need of Field3 and also display subtotal labels dynamically shown in sample.

[Anonymous]

In fact, there is a way to hide columns in pivot - by setting columnwidth to 0. I often use this macro as a workaround:

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

[#6]

Check out this discussion:

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

Montal.

[#7]

Thanks for the help Michael.

Finally Need to have the text in subtotals dynamically.

[#8]

I had added the action in the trigger of onactive trigger of the sheet. But the macro is not getting triggered . When I'm pressing the test button of the macro it is getting executed.

Please suggest why the macro is not getting triggered ?

Addtionally in the label of partial sum, how the field values can be appended dynamically like it is shown in the attached file ?

[#9]

try this, add this to your macro:

ActiveDocument.GetApplication.WaitForIdle

[#10]

I have added the line at the begining of the macro but no improvement.

Additionally in terms of Sorting, in the Sort Tab for field3 I have made expression as checked and inside expression inputbox I have added the text "=FieldSeq1" but Field3 is still sorted alphabetically. Any further suggestion ?