Totals/Aggregations on a Pivot Table

Wed, 13 Jan 2016 17:12:49 GMT

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

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:

Is this something QlikView can do?

Re: Totals/Aggregations on a Pivot Table

Gysbert_Wassenaar — Wed, 13 Jan 2016 18:14:01 GMT

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.