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.
set chart = ActiveDocument.GetSheetObject("CH1324")
set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = not gp.TableProperties.PivotAlwaysFullyExpanded
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:
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.