Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I found this post where it is shown, how to activate subtotals in a pivot table. "Show partial sum"
This works really nice. Show/Hide Subtotal in Pivot Table
I wasn't able to find in API or here in forum any setting, how to activate this setting when a pivot table is switched into straight table. this is called "Show totals".
I've prepared an example, if you have time to check it out ... I'm sure this is only one additional line starting with something like:
dims(i). ...... show totals ...... = true but I can not find it anywhere.
Thank you in advance for any hints.
Regards
Stan
Hi,
you can use this one:
sub xxx
set chart = ActiveDocument.GetSheetObject("CH01")
set cp = chart.GetProperties
set dims = cp.Dimensions
'dims(0).TotalString.v = "xyz"
dims(0).OtherTotalSpec.TotalMode = 1 '1 = on, 0 = off
chart.SetProperties cp
end sub
Cheers,
Martin
You need the object-typ to choose the right statement. Try this:
set obj = ActiveDocument.GetSheetObject("CH01")
if obj.GetObjectType = 10 then
set cp = chart.GetProperties
set dims = cp.Dimensions
dims(0).ShowPartialSums = false 'do not show sums
- chart.SetProperties cp
else
set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData
- expr.UsePartialSum = true
- chart.SetProperties cp
end if
and see follow examples from APIGuide.qvw
set chart = ActiveDocument.ActiveSheet.CreateStraightTable
- chart.AddDimension "Customer"
- chart.AddDimension "ProductType"
- chart.AddExpression "sum(Amount)"
set cp = chart.GetProperties
set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData
- expr.UsePartialSum = true
- chart.SetProperties cp
set chart = ActiveDocument.Activesheet.CreatePivotTable
- chart.AddDimension "ProductType"
- chart.AddExpression "sum(Amount)"
- chart.AddExpression "count(Customer)"
set cp = chart.GetProperties
set dims = cp.Dimensions
dims(0).ShowPartialSums = false 'do not show sums
- chart.SetProperties cp
rem ** remove borders from all listboxes on active sheet **
set x = ActiveDocument.ActiveSheet.GetSheetObjects
Objects = x.SheetObjects
For i = lBound(Objects) To uBound(Objects)
set obj = Objects(i)
if obj.GetObjectType = 1 then 'list boxes
set p = obj.GetProperties
p.Layout.Frame.BorderEffect = 0 'no border
obj.SetProperties p
end if
next
- Marcus
Hi Marcus,
nice of you, thanks for the reply. I found almost all of the documentation in API. However what I'm searching for is to activate following checkbox. Show total for DIMENSIONS (not for expressions).
so therefore I do not need Data.ExpressionData but "Dimension.Number.ShowTotal = True" or something like this in order to display, like in pivot, subtotals for each dimension, not the whole sum for each key figure.
Regards
Stan
Try some parts from here:
set chart = ActiveDocument.Activesheet.CreateStraightTable
chart.AddDimension "Product"
chart.AddExpression "sum(Amount)"
set gp = chart.GetProperties
set tp = gp.TableProperties
tp.MaxNumberShown = 2 'limit rows shown
tp.StraightShowOthers = true 'show others row
gp.GraphLayout.OtherString.v = "Other products" 'label for others row
tp.StraightTotalPosition = 1 'last row
tp.UseTotalLabelForStraight = true 'use total label
set e0 = gp.ExpressionDatas(0) 'first expression
e0.BrutalSum = true 'sum of rows
chart.SetProperties gp
- Marcus
Marcus,
command "usetotallabelforstraight" sounds good, but this will fail in my case. I think the macro line should start with dims(i). ..... totals ....... and end with = true.
Stan
Try this:
set e0 = gp.ExpressionDatas(0) 'first expression
e0.BrutalSum = true 'sum of rows
- Marcus
Marcus,
this will activate totals for each key figure, not for each dimension group.
Thank you anyway
Stan
Stan,
This is a bit rudimentary, but you might consider just creating a second chart without the partial sums which is hidden until a setting is changed. The setting change can be a variable (slider object) or an island field (always one selected value).
Attached is an example of using a field selection to hide/show the chart.
Indeed, Tyler, rudimentary, nice and working. However ...
I'm trying to build up an adhoc reporting (you name it Reports in What's new in QlikView 11.qvw) and what a pitty, it is not possible to identify if user wants or not subtotals and for which element in a row.
Otherwise there would me so many versions of tables around.
Thanks anyway.
Stan
Yes, I have explored a few options to get this type of customization for users you are describing.
You can extend that field selection approach to set the hide/show of a specific column or expression. Again, rudimentary, but might do the trick.
See attached.