Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Provide Sample data or sample app..
Count(Days) / Count(Total Days)
or
SUM(Days) / Sum(Total Days)
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.
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)
What is the dimensions you have used in your chart/table?
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.
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.
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.
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.