topic Re: Pivot table in QlikView

Pivot table

brindlogcool — Sun, 31 Mar 2013 02:35:43 GMT

Hi,

Is there any way we can expand all the columns in the pivot table using trigger.

Ideally i would like to expand all the columns and export to excel.

Right now we have to expand each and every column through right click or have to select always fully expanded or use the macro. Other than these options is there any way i can expand all the column in one click.

Re: Pivot table

Jason_Michaelides — Sun, 31 Mar 2013 18:03:32 GMT

Nope - afraid not. We are using a button that fires a macro to do it.

Hope this helpa,

Jason

Re: Pivot table

Fri, 26 Apr 2013 18:59:29 GMT

Hi Jason,

Could you pls share the macro? I am trying to do exactly that but I do not know enough to code the macro by myself.

Thanks!

Marina

Re: Pivot table

Jason_Michaelides — Mon, 29 Apr 2013 09:03:32 GMT

The code below sets the "always fully expand" property so you will most likely need a "collapse" button too to uncheck it. vChartToExpand is a variable with the ObjectID of the chart to expand/collapse.

Hope this helps,

Jason

Sub ExpandChart
'This macro fully expands the selected pivot table.
On Error Resume Next

Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
Set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = TRUE 'not gp.TableProperties.PivotAlwaysFullyExpanded
chart.SetProperties gp
End Sub

Sub CollapseChart
'This macro allowse collapsing of the selected pivot table.
On Error Resume Next

Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
Set gp = chart.GetProperties
gp.TableProperties.PivotAlwaysFullyExpanded = FALSE 'not gp.TableProperties.PivotAlwaysFullyExpanded
chart.SetProperties gp
End Sub