
Re: Conditional Subtotal of Pivot Table
Stefan Wühl Oct 29, 2013 12:31 PM (in response to Jihan Wang)Should be possible.
In your expression, you can use Dimensionality() to determine if the calculation is performed on subtotal or detail level, then conditionally choose an appropriate calculation.
avg() function should not take into consideration text values, so 'N/A' should be dismissed by default.
To tell you more, I think you need to tell us more details about the expression used etc.

Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 12:41 PM (in response to Stefan Wühl )Hi Thank you for your quick reply. The following is my expression for the calculation of performance points column:
=
if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0)))))HPIP_Max is the maximum performance value, HPIP_Min is the minimum performance value. I know if hard to figure out but basically, it checks how the current performance stands between the max and min value and assign a points.
Could you please explain dimensionality function a bit more. I used the help in QV but couldn't quite get it.
Thank you so much!

Re: Conditional Subtotal of Pivot Table
Stefan Wühl Oct 29, 2013 12:48 PM (in response to Jihan Wang)It's not really complicated, have you looked at the samples in the Help file?
Just create another expression,
=Dimensionality()
and check the results in your table, with a subtotal for Year dimension, you should get values 3 for detail lines and 1 for subtotal lines, if I see it correctly. So you can check against these values to decide which calculation to perform.
In your above expression, Performance is an expression label, right?
You might need to use advanced aggregation and the original expressions instead the label to calculate your average.

Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 12:50 PM (in response to Stefan Wühl )Yes, Denominator and Performance are all expression labels? They would cause any problem?

Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 1:20 PM (in response to Stefan Wühl )Well, the problem that I'm now facing is that for the subtotal row, there is no Threshold values (i.e. HPIP_Max and HPIP_Min) in the performance point calculation formula. I just want it to look at all the row above and get the average of Performance Points. Is there a way to do so?
Sorry for keep bugging you, I'm quite new to QV and there's so much to learn.
Thanks again for your patience!




Re: Conditional Subtotal of Pivot Table
Tresesco B Oct 29, 2013 12:35 PM (in response to Jihan Wang)Use expression something like:
=If(Dimensionality()<>1, YourExpressionForPerformance, Avg(Amountfield))
And then, enable Subtotal in presentation tab.

Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 12:41 PM (in response to Tresesco B )Could you please explain more about the dimensionality function for send me some reference link?
Thank you so much!

Re: Re: Conditional Subtotal of Pivot Table
Tresesco B Oct 29, 2013 12:53 PM (in response to Jihan Wang)From help:
Returns the number of dimension columns that have nonaggregation content. i.e. do not contain partial sums or collapsed aggregates.
A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.
This function is only available in charts. For all chart types except pivot table it will return the number of dimensions in all rows except the total, which will be 0.
PFA for understanding with example

Pivot_Subtotal.qvw 150.8 K

Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 1:13 PM (in response to Tresesco B )Thank you Tresesco for the great example. Now I figured out the dimentionality() function; however, QV doesn't seem to like my expression:
if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0)))))Denominator and Performance are both lables for some calculated expressions. If I want to calculate the average of the above expression, what would be the correct way?
Thank you again for your time and patience.

Re: Re: Conditional Subtotal of Pivot Table
Tresesco B Oct 29, 2013 3:02 PM (in response to Jihan Wang)May be like:
=
If(
Dimensionality<>1,
RangeSum
(
if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))))))
, RangeAvg
(
if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1(HPIP_Max/100Performance)/(HPIP_Max/100HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))))))
)

Re: Re: Conditional Subtotal of Pivot Table
Jihan Wang Oct 29, 2013 5:08 PM (in response to Tresesco B )I was able to get it work by using avg(agg) function
Thanks so much!




