11 Replies Latest reply: Oct 29, 2013 5:08 PM by Jihan Wang RSS

    Conditional Subtotal of Pivot Table

    Jihan Wang


      Hi QV Community folks,

       

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

      PivotTableScreenshot.png

       

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

                  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
                Tresesco B

                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

                    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

                        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
                            Jihan Wang

                            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/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)))))

                             

                            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

                                May be like:

                                =

                                If(

                                Dimensionality<>1,

                                  RangeSum

                                  (

                                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)))))

                                  )

                                , 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)))))

                                    )

                                )