Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Hide column in pivot table

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

13 Replies
Not applicable
Author

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?

Not applicable
Author

Hi,

Jochem is right.

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

Not applicable
Author

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
Not applicable
Author

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


Not applicable
Author

Check out this discussion:

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

Montal.

Not applicable
Author

Thanks for the help Michael.

Finally Need to have the text in subtotals dynamically.

Not applicable
Author

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 ?

Not applicable
Author

try this, add this to your macro:

ActiveDocument.GetApplication.WaitForIdle

Not applicable
Author

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 ?