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

# Pivot Table Totals Row

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

Provide Sample data or sample app..

Count(Days) / Count(Total Days)

or

SUM(Days) / Sum(Total Days)

• ###### Re: Pivot Table Totals Row

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

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

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

• ###### Re: Pivot Table Totals Row

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.

• ###### Re: Pivot Table Totals Row

What would you want to see as the %age for RUB? It seems that %age currently getting calculated is 28/489 and 383/489, where 489 is the grand total.

• ###### Re: Pivot Table Totals Row

I need to see a grand total view.

For example for RUB I need to see 411/489 or 84.05% (383 + 28).  This is the only one in this example.  Another facility could have RUC and others in the Med A Like category and I would need the same thing for all of them.

It is looking like I will need a separate chart/sheet to display the grand totals.

• ###### Re: Pivot Table Totals Row

I think this will work.

I created a copy of this pivot table and put on another sheet.

I then removed the dimension CategoryDesc.

Now my totals match what I think I need

I really wish I could display a Grand Total line in a pivot table.