Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to set "Subtotals on Top" in a Pivot Table via Macro?

Is is possible to set "Subtotals on Top" in a Pivot Table via Macro?
I have different dimensions and need a solution for setting "Subtotals on Top" individually.

Thanks for your help.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hello,

Check the following code:

Sub ChangeSubtotalPos Set c = ActiveDocument.GetSheetObject("CH01") '' Your chart ID here Set cp = c.GetProperties Set d = cp.Dimensions For i = 0 To d.Count -1 '' assuming all dimensions have partial sums d(i).ShowPartialSums = true '' make sure you are showing partial sums Next cp.TableProperties.PivotTableTotalPosition = 0 '0 = top; 1 = bottom c.SetProperties cpEnd Sub


This will show partial sums and in top for first dimension ("d(0)", bold). If you want to show every dimension then you will need to loop through all the dimensions.

Hope that helps.

EDIT: Added loop, please check syntax

View solution in original post

1 Reply
Miguel_Angel_Baeyens

Hello,

Check the following code:

Sub ChangeSubtotalPos Set c = ActiveDocument.GetSheetObject("CH01") '' Your chart ID here Set cp = c.GetProperties Set d = cp.Dimensions For i = 0 To d.Count -1 '' assuming all dimensions have partial sums d(i).ShowPartialSums = true '' make sure you are showing partial sums Next cp.TableProperties.PivotTableTotalPosition = 0 '0 = top; 1 = bottom c.SetProperties cpEnd Sub


This will show partial sums and in top for first dimension ("d(0)", bold). If you want to show every dimension then you will need to loop through all the dimensions.

Hope that helps.

EDIT: Added loop, please check syntax