Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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.
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...).
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
Thanks a lot Marcus!! I have added a document with a sample showing how your macro works .
Really grateful for your help
Juan