
Re: Pivot Table Sum of Partial Sum
Telmo Duarte Nov 12, 2013 7:05 AM (in response to Jihan Wang)Hi Jihan,
I'm facing a similar task. Did you manage to find a solution?

Re: Pivot Table Sum of Partial Sum
Jihan Wang Nov 12, 2013 9:04 AM (in response to Telmo Duarte )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
Muralidhar Koti Nov 12, 2013 7:12 AM (in response to Jihan Wang)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
Jonathan Dienst Nov 12, 2013 7:36 AM (in response to Jihan Wang)Hi
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
Jihan Wang Nov 12, 2013 9:03 AM (in response to Jonathan Dienst )Thank you Jonathan. I will try out the solution you suggested.

Re: Pivot Table Sum of Partial Sum
Telmo Duarte Nov 14, 2013 1:17 PM (in response to Jonathan Dienst )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.

