This content has been marked as final. Show 2 replies
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.
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:
on error resume next
for n = 0 to 5 ' number of dimensions was 5
set chart = ActiveDocument.GetSheetObject("CH01")
chart.ExpandLeft 0, n, true
All suggestions would be helpful!