topic Re: Pivot Table Sum of Partial Sum in QlikView

Pivot Table Sum of Partial Sum

Thu, 31 Oct 2013 19:16:37 GMT

Hello Folks,

Below is the screenshot of my pivot table for 1 facility:

I was able to customize my partial sum of dimension "Year" to calculate the AVERAGE of the points for each facility for each year (i.e. the "Points" row). At the last row, I would like to do the SUM of 'Points' row of ALL hospitals to get the total score of all the facilities in the system. I am aware that I need to use the dimensionality() function and sum advanced aggregation function that I used to get the average; however, my expression does not seem to work, what I tried is something like:

if(dimentionality=0, sum({$<Dimentionality={1}>} aggr(performance points, Measure, Year, Facility))).

Can anyone provide me with some guidance.

Thank you so much!

Re: Pivot Table Sum of Partial Sum

tduarte — Tue, 12 Nov 2013 12:05:34 GMT

Hi Jihan,

I'm facing a similar task. Did you manage to find a solution?

Re: Pivot Table Sum of Partial Sum

Tue, 12 Nov 2013 12:12:36 GMT

try this..

if(dimentionality=0, sum(total [performance points])).

This statement basically ignores all dimentioned and only considers the current selection. Alternatively you can also set the method of sub total to 'Sum of Rows' in the expression tab.

It would be heloful if you could post a sample QVW, so that we all can try to help you with a solution.

Re: Pivot Table Sum of Partial Sum

jonathandienst — Tue, 12 Nov 2013 12:36:41 GMT

I am not sure of the solution to your problem. If you dont want to post a sample qvw, at least provide the expression that gives you [performance points] and the expression for the average on the year total.

To use Dimensionality(), you can do something like this:

Pick(Dimensionality()+1,

expression for grand (facilitiesd) total,

expression year total,

expression for lowest level

)

Replace the italics with the expressions for each level. Note that Dimensionality() is a function, so you need the parentheses, and you cannot use Dimensionality in a set expression, as the set expression is evaluated once for the chart/table (before the individual lines/dimensions exist). Using Pick() like above makes it easy to add or remove levels and their expressions.

HTH

Jonathan

Re: Pivot Table Sum of Partial Sum

Tue, 12 Nov 2013 14:03:14 GMT

Thank you Jonathan. I will try out the solution you suggested.

Re: Pivot Table Sum of Partial Sum

Tue, 12 Nov 2013 14:04:11 GMT

Not really.

The way that I eventually got this work out is to use double advanced aggregate function. First for the average, then for the sum. But the expressnion ends up really complicated...

Re: Pivot Table Sum of Partial Sum

tduarte — Tue, 12 Nov 2013 16:34:46 GMT

Hi Jonathan,

The Pick(Dimensionality()+1 sounds like a good solution as long as Pivoting is not allowed.

Please have a look at my discussion below, any help appreciated.

I doubt that it is possible but I need to aggregate dynamically based on the pivot dimension PLUS an extra one. This way I could allow pivoting.

http://community.qlik.com/thread/98380

For example:

for 1st dimension in pivot - Aggr(Sum(Sales),1stDimension,ResellerID)

for 2nd dimension in pivot - Aggr(Sum(Sales),1stDimension,2ndDimension,ResellerID)

...

Resuming, the default pivot aggregation plus ResellerID - a dimension not in the pivot.

Re: Pivot Table Sum of Partial Sum

Anonymous — Wed, 28 Jan 2015 06:03:56 GMT

Hi jonathan i have query regarding the pivot table as i want the sum of final performance.

i worte the expression for the Final performance

=if(Dimensionality()=1,

[Coperate SLP]* [Performance],

as of now the dimensionality()=0 .