Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expanding only first level of Pivot table

Help with macro!

I have a macro related problem for expanding only the first dimension of a pivot table. As the table is data heavy, I wish to expand only the 1st dimension, and not the rest. So i need to make sure that after every refresh, all the levels have been rolled up. So that the next time I expand the 1st dimension of the pivot table, only the next dimension shows up, when I expand the second dimension, the third shows up ...

I looked on the qlik community and found the following code: but this one doesnt roll up all the dimensions. So if i have expanded the whole pivot table, and then i collapse it, followed by a clear filter. The next time i expand the pivot table the rest of the dimensions are still expanded.

sub toggle_expand()

set chart = ActiveDocument.GetSheetObject("CH1324")

set gp = chart.GetProperties

gp.TableProperties.PivotAlwaysFullyExpanded = not gp.TableProperties.PivotAlwaysFullyExpanded

chart.SetProperties gp

end sub

 

I also thougth of using dimensionality but don't know where it could fit in.

 

I also tried the following loop, but it didnt work:

sub ExpandPivot

on error resume next

for n = 0 to 5 ' number of dimensions was 5

set chart = ActiveDocument.GetSheetObject("CH01")

chart.ExpandLeft 0, n, true

next

end sub

All suggestions would be helpful!

Regards,

2 Replies
Gysbert_Wassenaar

I think the only option is to use a bookmark and check Include Layout State. You can the use the OnReload trigger to activate the bookmark. The pivot table should then revert to the state it was in when you created the bookmark.


talk is cheap, supply exceeds demand
Not applicable
Author

Hello Gysbert Wassenaar,

Indeed a creative solution! Will test it.

Other solutions are welcome too.

Thank you!