topic Conditional Subtotal of Pivot Table in QlikView

Conditional Subtotal of Pivot Table

Tue, 29 Oct 2013 16:21:23 GMT

Hi QV Community folks,

I am facing some problem with pivot table subtotals. Here is the screen shot of my current pivot table

I wiped out the sensitive information, but what I want is that for each year (second dimension) my subtotal calculates the AVERAGE of the "performance points" column for all rows except for the rows with 'N/A'. Is there a way to accomplish this? The "performance points" is a calculated fields with nested if statement because of the complexity of the determination of the points

Thank you so much for any helpful guidance!

Best Regards,

Jihan

Re: Conditional Subtotal of Pivot Table

swuehl — Tue, 29 Oct 2013 16:31:01 GMT

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

tresB — Tue, 29 Oct 2013 16:33:43 GMT

Use expression something like:

=If(Dimensionality()<>1, YourExpressionForPerformance, Avg(Amountfield))

And then, enable Subtotal in presentation tab.

Re: Conditional Subtotal of Pivot Table

Tue, 29 Oct 2013 16:41:06 GMT

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/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_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

Tue, 29 Oct 2013 16:41:42 GMT

Could you please explain more about the dimensionality function for send me some reference link?

Thank you so much!

Re: Conditional Subtotal of Pivot Table

swuehl — Tue, 29 Oct 2013 16:48:56 GMT

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

Tue, 29 Oct 2013 16:50:48 GMT

Yes, Denominator and Performance are all expression labels? They would cause any problem?

Re: Re: Conditional Subtotal of Pivot Table

tresB — Tue, 29 Oct 2013 16:53:52 GMT

From help:

dimensionality ( )

Returns the number of dimension columns that have non-aggregation 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

Re: Conditional Subtotal of Pivot Table

Tue, 29 Oct 2013 17:13:05 GMT

Thank you Tresesco for the great example. Now I figured out the dimentionality() function; however, QV doesn't seem to like my expression:

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: Conditional Subtotal of Pivot Table

Tue, 29 Oct 2013 17:20:36 GMT

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: Re: Conditional Subtotal of Pivot Table

tresB — Tue, 29 Oct 2013 19:02:45 GMT

May be like:

If(

Dimensionality<>1,

RangeSum

(

)

, RangeAvg

(

    if(Denominator=0,'N/A',if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0))<0.99,0,if(if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0))>5,5,if(HPIP_Max<>HPIP_Min,1+(1-(HPIP_Max/100-Performance)/(HPIP_Max/100-HPIP_Min/100))*4,
    if(Performance>=HPIP_Min/100,5,0)))))

)

Re: Re: Conditional Subtotal of Pivot Table

Tue, 29 Oct 2013 21:08:46 GMT

I was able to get it work by using avg(agg) function

Thanks so much!