Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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.
Hello Gysbert Wassenaar,
Indeed a creative solution! Will test it.
Other solutions are welcome too.
Thank you!