Pivot table show/hide subtotal by macro.

[juan_c_martinez]

Is there any way to create a macro that allows to hide/show dimension subtotals in a pivot table?

I found:

- Tricks to do that for example using twin dimensions, one with total and other without and showing both alternatively,

- In an straight table Qlikview show totals for dimensions in straight table activate per macro (I look for a macro similar to this one )

Thanks in advance!!

Juan C.

[Gysbert_Wassenaar]

If you're using QV11 it's easier to add the dimension twice, once with partial sums enabled and once without, and use the conditional display expression to toggle between the two. See attached example.

[juan_c_martinez]

Gysbert,

I already knew this trick and I have used it (see my post) but the problem is when the user change the sequence or pivot the dimension, the dimension hidden remain  in the place last time is was shown .

Any case thanks a lot.

Juan C.

[#4]

Hi Juan.

You could predefine the dimensions used on your pivot table using data islands and afterwards use Gysbert Wassenaar solution (the order problem is solved...).

[marcus_sommer · Accepted solution]

In this case partial sums will be showed on selections of a certain field and checked and set in loop - for further information look in APIGuide.qvw in your install-folder. If you handled with bigger objects and large amounts of data it could be rather slow and it won't work on AJAX - so the tip from Gysbert Wassenaar could be better suit.

sub PartialSums

dim chart, prop, dims, pSums, str, i, j 'Variablen deklarieren

set doc = ActiveDocument
set pSums  = doc.Fields("_PartialSums").getSelectedValues 'selektierte Werte vom Auswahlfeld in ein Array einlesen

set chart = doc.GetSheetObject("CH01") 'Pivot auswählen
prop = chart.GetProperties 'Zugriff auf Eigenschaften
set dims = prop.Dimensions 'Auswahl der Dimensionseigenschaften

for i = 0 to dims.Count - 1 'Pivotdimensionen durchlaufen und auslesen
    dims(i).ShowPartialSums = false 'für alle Dimensionen die Teilergebnisse rausnehmen
    for j = 0 to pSums.Count - 1 'selektierte Werte des Auswahlfeldes werden durchlaufen und ausgelsen
        if dims.Item(i).Title.v = pSums.Item(j).Text then dims(i).ShowPartialSums = true 'Vergleich des Auslesens + setzen des Teilergbenis
    next
    'str = str & dims.Item(i).Label.v & chr(10)
next

chart.SetProperties prop 'Zuweisung der Sichtbarkeiten für die Teilergebnisse für die Dimensionseigenschaften der Pivot

set doc = nothing 'Variablen löschen
set pSums = nothing
set chart = nothing
set dims = nothing

end sub

- Marcus

[juan_c_martinez]

Thanks a lot Marcus!!  I have added a document with a sample showing how your macro works .

Really grateful for your help

Juan