topic Re: Re: Pivot table show/hide subtotal by macro. in QlikView

Pivot table show/hide subtotal by macro.

juan_c_martinez — Mon, 03 Feb 2014 11:38:59 GMT

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.

Re: Pivot table show/hide subtotal by macro.

Gysbert_Wassenaar — Mon, 03 Feb 2014 11:54:44 GMT

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.

Re: Pivot table show/hide subtotal by macro.

juan_c_martinez — Mon, 03 Feb 2014 14:04:00 GMT

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.

Re: Pivot table show/hide subtotal by macro.

Fri, 22 Aug 2014 12:54:05 GMT

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...).

Re: Re: Pivot table show/hide subtotal by macro.

marcus_sommer — Fri, 22 Aug 2014 15:09:51 GMT

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

Re: Re: Pivot table show/hide subtotal by macro.

juan_c_martinez — Thu, 28 Aug 2014 07:47:55 GMT

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

Really grateful for your help

Juan