Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Totals/Aggregations on a Pivot Table

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?

1 Reply
MVP & Luminary
MVP & Luminary

Re: Totals/Aggregations on a Pivot Table

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.


talk is cheap, supply exceeds demand