topic Re: Qlikview show totals for dimensions in straight table activate per macro in QlikView

Qlikview show totals for dimensions in straight table activate per macro

Fri, 06 Sep 2013 06:50:06 GMT

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

Re: Qlikview show totals for dimensions in straight table activate per macro

marcus_sommer — Fri, 06 Sep 2013 11:47:24 GMT

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

Re: Qlikview show totals for dimensions in straight table activate per macro

Mon, 16 Sep 2013 09:04:00 GMT

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

Re: Qlikview show totals for dimensions in straight table activate per macro

marcus_sommer — Mon, 16 Sep 2013 11:17:52 GMT

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

Re: Qlikview show totals for dimensions in straight table activate per macro

Mon, 16 Sep 2013 12:27:47 GMT

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

Re: Qlikview show totals for dimensions in straight table activate per macro

marcus_sommer — Mon, 16 Sep 2013 12:44:13 GMT

Try this:

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

e0.BrutalSum = true 'sum of rows

- Marcus

Re: Qlikview show totals for dimensions in straight table activate per macro

Mon, 16 Sep 2013 12:56:36 GMT

Marcus,

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

Thank you anyway

Stan

Re: Qlikview show totals for dimensions in straight table activate per macro

Tyler_Waterfall — Thu, 26 Sep 2013 13:16:10 GMT

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.

Re: Qlikview show totals for dimensions in straight table activate per macro

Thu, 26 Sep 2013 14:31:57 GMT

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

Re: Re: Qlikview show totals for dimensions in straight table activate per macro

Tyler_Waterfall — Thu, 26 Sep 2013 15:58:59 GMT

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.

Re: Qlikview show totals for dimensions in straight table activate per macro

Fri, 27 Sep 2013 06:42:45 GMT

Tyler, this selection is nice and works again fine. Maybe I didn't express myself correct, sorry for that. With subtotals I mean sum after each end of dimension group, not just a sum for the whole keyfigure. Please have a look at the screen shot. This trick does in pivot table do subtotals (Presentation - Subtotals at top/bottom) and in straight table (Dimension Limits - Show Total).

Re: Qlikview show totals for dimensions in straight table activate per macro

Tyler_Waterfall — Fri, 27 Sep 2013 14:09:20 GMT

Could you do the same thing with hide/show but just have the specific charts showing --- the subtotals?

Re: Qlikview show totals for dimensions in straight table activate per macro

Mon, 07 Oct 2013 10:12:17 GMT

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

Re: Re: Qlikview show totals for dimensions in straight table activate per macro

Tyler_Waterfall — Mon, 07 Oct 2013 12:16:58 GMT

Stan,

There is a new Demo app on the QlikView site that has a "Build your own chart" setup that might be interesting to you. You can find the "Call Detail Record Analysis" app here: http://us.demo.qlik.com/detail.aspx?appName=Call%20Detail%20Record%20Analysis.qvw

Look on the "Report" tab. Just another idea.

Tyler

Re: Qlikview show totals for dimensions in straight table activate per macro

Tue, 08 Oct 2013 06:17:22 GMT

This is what I was looking for.

dims(0).OtherTotalSpec.TotalMode = 1

Re: Qlikview show totals for dimensions in straight table activate per macro

Tue, 08 Oct 2013 06:39:05 GMT

Tyler, this application is really nice. The Report tab is nothing such a pretty new. I saw those in many other qvw demos as well. The question is, if such a report tab will be implemented, how would you let the web (portal, ajax, iis, plugin, small device or whatever) report consumer activate or deactivate subtotals for chosen characteristics?

Just with tricks, define new table and hide or unhide those is with possible combinations nothing practicable.

Re: Qlikview show totals for dimensions in straight table activate per macro

Fri, 11 Oct 2013 11:58:19 GMT

Tyler, if you are so curious, have a look on my post. Maybe you could contribute to improvements for all QlikView users.

Adhoc reporting with QlikView, cyclic groups, order and subtotals issue

When will be released the newest Governance Dashboard?

Regards

Stan

Re: Qlikview show totals for dimensions in straight table activate per macro

Tyler_Waterfall — Fri, 11 Oct 2013 13:37:48 GMT

Thank you Stan for sharing this. Yes, I have felt the need for easier customization on front-end QlikView applications. Don't give up hope on QlikView, though!

We do not have a published release date for the 1.1 Governance Dashboard, though we are targeting Q4 (calendar). Sorry I can't be more specific yet, except that there will be a lot of great improvements in the 1.1 version!

Tyler