Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

to set a pivot subtotal with macro

hi,

It' s possible to set a pivot subtotal with macro?

thank you very much

1 Solution

Accepted Solutions
Not applicable
Author

Sub SetSubTotalPivotTable

     Set c = ActiveDocument.GetSheetObject("CH01")

     Set cp = c.GetProperties

     Set d = cp.Dimensions

     For i = 0 to d.Count - 1

          d(i).ShowPartialSums = true

     Next

     cp.TableProperties.PivotTablePosition = 0

     c.SetProperties cp

End Sub

View solution in original post

4 Replies
Anonymous
Not applicable
Author

From the V10 API Guide:

set chart = ActiveDocument.Activesheet.CreatePivotTable
chart.AddDimension "ProductType"
chart.AddExpression "sum(Amount)"
chart.AddExpression "count(Customer)"
set cp = chart.GetProperties
set dims = cp.Dimensions
dims(0).ShowPartialSums = false    'do not show sums
chart.SetProperties cp


You will want to change the dims(0).ShowPartialSums = true

-Phil

Not applicable
Author

Sub SetSubTotalPivotTable

     Set c = ActiveDocument.GetSheetObject("CH01")

     Set cp = c.GetProperties

     Set d = cp.Dimensions

     For i = 0 to d.Count - 1

          d(i).ShowPartialSums = true

     Next

     cp.TableProperties.PivotTablePosition = 0

     c.SetProperties cp

End Sub

Not applicable
Author

Sub SetSubTotalPivotTable

     Set c = ActiveDocument.GetSheetObject("CH01")

     Set cp = c.GetProperties

     Set d = cp.Dimensions

     For i = 0 to d.Count - 1

          d(i).ShowPartialSums = true

     Next

     cp.TableProperties.PivotTablePosition = 0

     c.SetProperties cp

End Sub

Not applicable
Author

Hello,

I tried it on the V11 but it work but without this line of macro :

cp.TableProperties.PivotTablePosition = 0