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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Macro to show/hide selected dimensions subtotals in the pivot table (list box)

Hello,

I want to write a macro that would show/hide subtotals for dimensions, that are selected from list box. I found a macro to show/hide subtotals for the table, that has regular dimensions (where you can't select which dimensions to show):

'***********************************************************************************************************************************************

' Show Subtotals

'***********************************************************************************************************************************************

sub PivotShowSubtotals

set c = ActiveDocument.GetSheetObject("CH01")

set cp = c.GetProperties

set dims = cp.Dimensions

'set gp = chart.GetProperties

'set tp = gp.TableProperties

countexpr = cp.expressions.count 'count expressions

countdimexpanded = c.getcolumncount - countexpr 'count expanded dimensions

'msgbox(c.getcolumncount - countexpr)

for i = 0 to countdimexpanded -1

j = i+1

Answer = MsgBox("Show Subtotals: Column "& j,3) '3 = yes, no, cancel

If Answer=6 Then 'yes

dims(i).ShowPartialSums = true

c.SetProperties cp

'''' show total from dimension limit tab

c.SetProperties cp

elseif Answer=7 Then 'no

dims(i).ShowPartialSums = false

c.SetProperties cp

else

exit for '(Answer=2) 'cancel

End If

next

end sub

'***********************************************************************************************************************************************

' Hide Subtotals

'***********************************************************************************************************************************************

sub PivotHideSubtotals

set c = ActiveDocument.GetSheetObject("CH01")

set cp = c.GetProperties

set dims = cp.Dimensions

for i = 0 to dims.Count -1

dims(i).ShowPartialSums = false

next

c.SetProperties cp

end sub

But I need a macro, which would show/hide subtotals for particular dimensions, selected from listbox.

Please, help me

1 Reply
marcus_sommer

I have built such show/hide sub-totals with macros and wouldn't recommend to use such approach then it's not necessary - see: Macros are Bad - you could gain the same if you doubled each of your dimensions within the chart - one with sub-totals and one without - and use visibility-conditions on these dimensions maybe per if(substringcount(getfieldselections(DimensionsWithSubTotals), 'Dimension1') >= 1, 1, 0).

There are various examples available here in the community how you could show and hide dimensionss and expressions, maybe this one: Hiding Dimensions in Pivot Table.

- Marcus