8 Replies Latest reply: Jun 28, 2016 11:09 AM by Mark Ritter RSS

    Pivot Table Totals Row

    Mark Ritter

      I have a column that calculates the % of  # Days.

      Out of the box the Pivot table is calculating this metric based on the highest level Row.

      So for example I have Row value A and Row Value B.  The percentage is calculated as the #Days/Total # Days for that Row Value. 

      What I need is to have the % calculated as the #Days/Total # Days for all Row Values. 

      Can this be done in a Pivot table or in Qlik Sense?

        • Re: Pivot Table Totals Row
          Manish Kachhia

          Provide Sample data or sample app..

           

          Count(Days) / Count(Total Days)

          or

          SUM(Days) / Sum(Total Days)

            • Re: Pivot Table Totals Row
              Mark Ritter

              That is exactly what I did.  But the % is being calculated on the Total Days for each section and not on the Grand Total of # Days.

                • Re: Pivot Table Totals Row
                  Sunny Talwar

                  I think what Manish has provided should work for you....

                   

                  Notice he has added any field name for the TOTAL qualifier. Are you sure you also don't have that?

                   

                  Count(TOTAL <FieldName> Days) vs Count(TOTAL Days)

                  • Re: Pivot Table Totals Row
                    Manish Kachhia

                    What is the dimensions you have used in your chart/table?

                      • Re: Pivot Table Totals Row
                        Mark Ritter

                        The highest level row is called CategoryDesc.

                         

                        It has 2 values 'Med A Like RUG Mix' and 'Med A RUG Mix'.

                         

                        I need the breakdown for each of these in the Pivot Table.  But I need the % calculation to show the % of the grand total and not a percentage of each value for CategoryDesc total.  The problem arises when there is a matching value under Med A Like RUG Mix such as RUB that also appears in the Medicare A RUG Mix.  All of the other percentages are ok because they don't exist in both categories.  But that will change from facility to facility.

                         

                        If this output can be replicated in a regular table I am open to that.  Or if there is a way to split these categories up into separate tables or sheets.  But I have not been able to figure out a way to do that like you can in QlikView.

                         

                        Here is what I am getting now.

                        Capture.PNG