7 Replies Latest reply: Jan 28, 2015 1:03 AM by Bunny V RSS

    Pivot Table Sum of Partial Sum

    Jihan Wang

      Hello Folks,

       

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

      Customized Partial Sum PivotTable.png

       

      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
          Telmo Duarte

          Hi Jihan,

           

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

          • Re: Pivot Table Sum of Partial Sum
            Muralidhar Koti

            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

              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