Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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


end sub

All suggestions would be helpful!


2 Replies

Re: Expanding only first level of Pivot table

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

Re: Expanding only first level of Pivot table

Hello Gysbert Wassenaar,

Indeed a creative solution! Will test it.

Other solutions are welcome too.

Thank you!

Community Browser