Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
rittermd
Master
Master

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?

8 Replies
MK_QSL
MVP
MVP

Provide Sample data or sample app..

Count(Days) / Count(Total Days)

or

SUM(Days) / Sum(Total Days)

rittermd
Master
Master
Author

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.

sunny_talwar

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)

MK_QSL
MVP
MVP

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

rittermd
Master
Master
Author

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.

Capture.PNG

sunny_talwar

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.

rittermd
Master
Master
Author

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.

rittermd
Master
Master
Author

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.