CODEWITHSUNDEEP
qlikview
Brandon Patrick
Brandon Patrick

Reputation: 73

Pivot Table Independent Grand Total Row

Is it possible to have a grand total row in a pivot table that is independent of selections? The table I am currently working with has region & branch dimensions and then several columns of data. I would like the company total to display at the bottom row regardless of what regions and/or branches are selected.

Upvotes: 0

Views: 5089

Answers (2)

Stefan Stoychev
Stefan Stoychev

Reputation: 5012

You can use Dimensionality() function.

As you can see from the picture below the rows have Dimensionality() = 2 and the total row have Dimensionality() = 0

enter image description here

So in your case the expression will be something like this:

if(  Dimensionality() = 0, 
       sum( {< Region=, Branch= >} Value), 
       sum( Value ) 
)

(Don't forget to remove/disable the Dimensionality() column to test it. If not removed the chart will not behave as normal)

Using the above expression the total row will show the sum( Value ) ignoring the selections in Region and Branch fields:

enter image description here

Also you can see that Dimensionality() is changing depends on the table aggregation. For example when collapse the Region the Dimensionality() function is returning 1 for the rows:

enter image description here

No need to tell you that if you have decent amount of data such expressions will decrease the performance!

There is also and SecondaryDimensionality() function which is basically the same as Dimensionality() but for the horizontal pivot dimensions.

Upvotes: 1

Chris J
Chris J

Reputation: 936

An easier way of accomplishing this would be to have a straight table directly below your pivot table using set analysis to exclude selections.

Upvotes: 1

Related Questions