1 Reply Latest reply: Jan 13, 2016 1:14 PM by Gysbert Wassenaar RSS

    Totals/Aggregations on a Pivot Table

    Andrew Nicholls

      I'd like the below putting into a pivot table/straight table but I am unsure how to do it.

       

      Image.PNG

       

      I would need VCV3 and VCV6 to be the totals.

       

      VCV3 = VCV2 + VCV1

      VCV6 = VCV3 + VCV4 + VCV5 + VCV6

       

      Ideally I would like something like this:

       

      Ideal.PNG

       

      Is this something QlikView can do?

        • Re: Totals/Aggregations on a Pivot Table
          Gysbert Wassenaar

          Yes, with a little qlik foo in the script. You can add an extra table that creates a new field to link the VC values to the totals.

           

          ReportDim

          LOAD * INLINE [

          ReportField, FIELD, IsTotal

          VC1, VC1, 0

          VC2, VC2, 0

          VC3, VC1, 1

          VC3, VC2, 1

          VC4, VC4, 0

          VC5, VC5, 0

          VC6, VC1, 1

          VC6, VC2, 1

          VC6, VC4, 1

          VC6, VC5, 1

          ];

           

          Then use the field ReportField as dimension in your pivot table instead of FIELD.

           

          You can use the IsTotal field to change the background color of the total rows and to change the text format to bold.

          Click on the + in front of the expression, select Background color and use =if(IsTotal=1, LightGray())

          Click on the + in front of the expression, select Text Format and use =if(IsTotal=1, '<B>')

          Do the same for the dimensions.