2 Replies Latest reply: Dec 4, 2012 8:42 AM by Abhinav Agarwal RSS

    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,