Skip to main content
Announcements
Get Ready. A New Qlik Learning Experience is Coming February 17! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikview show totals for dimensions in straight table activate per macro

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

17 Replies
marcus_sommer

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

  1. chart.SetProperties cp

else

set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData

  1. expr.UsePartialSum = true
  2. chart.SetProperties cp

end if

and see follow examples from APIGuide.qvw

set chart = ActiveDocument.ActiveSheet.CreateStraightTable

  1. chart.AddDimension "Customer"
  2. chart.AddDimension "ProductType"
  3. chart.AddExpression "sum(Amount)"

set cp = chart.GetProperties

set expr = cp.Expressions.Item(0).Item(0).Data.ExpressionData

  1. expr.UsePartialSum = true
  2. chart.SetProperties cp

set chart = ActiveDocument.Activesheet.CreatePivotTable

  1. chart.AddDimension "ProductType"
  2. chart.AddExpression "sum(Amount)"
  3. chart.AddExpression "count(Customer)"

set cp = chart.GetProperties

set dims = cp.Dimensions

dims(0).ShowPartialSums = false    'do not show sums

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

Not applicable
Author

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.

subtotals straight table.JPG.jpg

Regards

Stan

marcus_sommer

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

Not applicable
Author

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

marcus_sommer

Try this:

set e0 = gp.ExpressionDatas(0)         'first expression

e0.BrutalSum = true                    'sum of rows

- Marcus

Not applicable
Author

Marcus,

this will activate totals for each key figure, not for each dimension group.

Thank you anyway

Stan

Tyler_Waterfall
Employee
Employee

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.

Not applicable
Author

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

Tyler_Waterfall
Employee
Employee

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.