Sum / Subtotal of Rows in a Pivot Table

shane_spencer — Thu, 13 Feb 2014 09:57:43 GMT

I've got a pivot table that contains the Average size of numerous TM1 Instances on several Servers, by Date.

My raw data is take at 5 minute Intervals but for this pivot table I calculate the Average over the day.

i.e.

(At a pinch I could use Max instead of Average).

From these Averages, I'm trying to also calculate the Sub Total or Sum of the TM1 Instances size by Server, for each date.

i.e

(At a pinch I'd settle for a seperate chart that shows Sub Totals only)

Doing some searching I thought this may be done via the Aggr function and found 2 pretty good posts from John Witherspoon , and Adam Bellerby. i.e.

Aggr Function

QlikView Technical Brief - AGGR.docx

The I found another good contirbution by Henric Cronström, that suggests Aggr should NOT be used for this purpose.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-used

So now I'm a bit confused. I've tried both methods but must admit that it's beyond me.

I've attached a much cut down version of my qvw in the hopes someone can help.

n.b. I've had to use Set Analysis that may seem redundant in the attached QVW but is needed to narrow down the data in the original QVW which is many times larger with dozens of Services and hundreds of Servers, Metrics and Submetrics.

Re: Sum / Subtotal of Rows in a Pivot Table

shane_spencer — Thu, 13 Feb 2014 14:56:48 GMT

I figured it out from an example posted by tresesco

Re: Re: Conditional Subtotal of Pivot Table

...using "Show Partial Sums" at Instance level...

I was able to display the Average Instance size by Server (not the Sum of the Individual Instance sizes by Server like I wanted).

I realised though that this could be fixed by multiplying the Average by the Count of the Distinct Instance names.

i.e. replacing:

( avg( {$<Service = {'Harmony'}, Role = {'Application Server'}, sub_Metric = {'Memory:Total Pool Bytes'} >} value) )/1024/1024/1024

with:

(( avg( {$<Service = {'Harmony'}, Role = {'Application Server'}, sub_Metric = {'Memory:Total Pool Bytes'} >} value) )/1024/1024/1024)*
(Count({$<Service = {'Harmony'}, Role = {'Application Server'}, sub_Metric = {'Memory:Total Pool Bytes'} >}Distinct(Main_metric)))

topic Re: Sum / Subtotal of Rows in a Pivot Table in QlikView

Sum / Subtotal of Rows in a Pivot Table

Re: Sum / Subtotal of Rows in a Pivot Table