How to compute a total in pivot table as the distribution of the total

[amweiner]

I created a pivot table that contains two dimensions (Category and Type). These are the raw numbers.

This is the original numeric data:
Category	Type	Type 1	Type 2	Type 3	Type 4	Type 5	Type 6	Type 7	Type 8	Type 9	Total
CAT1		2,884,362	139,386	496,090	188,193	240,506	1,796,704	2,094,880	223,261	0	8,063,382
CAT2		535,058	24,751	2,730	309,471	0	15,556	0	38,126	0	925,692
CAT3		16,656,022	578,886	2,256,423	1,741,530	2,372,081	369,565	30,588	609,552	0	24,614,647
CAT4		10,026,812	356,542	1,318,808	1,443,051	647,855	955,216	2,015,666	793,732	0	17,557,682
CAT5		123,739	60,385	897,485	719,268	788	405,526	23,260	121,289	2,600	2,354,340
CAT6		13,130,509	451,842	1,015,460	748,945	386,240	934,207	1,800,988	618,707	0	19,086,898
CAT7		1,210,356	40,128	82,720	42,152	117,472	39,188	45,031	126,151	0	1,703,198
CAT8		178,228	8,826	5,167	375,103	30,470	187,483	2,803,386	148,450	0	3,737,113
Total		44,745,086	1,660,746	6,074,883	5,567,713	3,795,412	4,703,445	8,813,799	2,679,268	2,600	78,042,952

I need to calculate these as percentages, so that each row is a distribution of that row, and totals to 100%. I used the following expression to do this. Everything is fine with the exception of the total line. I want the total line to be a distribution across the total, not the sum of each type.

sum(AMT) / aggr(NODISTINCT sum(AMT),Category)

Category	Type	Type 1	Type 2	Type 3	Type 4	Type 5	Type 6	Type 7	Type 8	Type 9	Total
CAT1		36%	2%	6%	2%	3%	22%	26%	3%	-	100%
CAT2		58%	3%	0%	33%	-	2%	-	4%	-	100%
CAT3		68%	2%	9%	7%	10%	2%	0%	2%	-	100%
CAT4		57%	2%	8%	8%	4%	5%	11%	5%	-	100%
CAT5		5%	3%	38%	31%	0%	17%	1%	5%	0%	100%
CAT6		69%	2%	5%	4%	2%	5%	9%	3%	-	100%
CAT7		71%	2%	5%	2%	7%	2%	3%	7%	-	100%
CAT8		5%	0%	0%	10%	1%	5%	75%	4%	-	100%
Total		-	-	-	-	-	-	-	-	0%	-

This is the desired result. How can I get qlikview to display the percentage distribution on the total row?

Category	Type	Type 1	Type 2	Type 3	Type 4	Type 5	Type 6	Type 7	Type 8	Type 9	Total
CAT1		36%	2%	6%	2%	3%	22%	26%	3%	0%	100%
CAT2		58%	3%	0%	33%	0%	2%	0%	4%	0%	100%
CAT3		68%	2%	9%	7%	10%	2%	0%	2%	0%	100%
CAT4		57%	2%	8%	8%	4%	5%	11%	5%	0%	100%
CAT5		5%	3%	38%	31%	0%	17%	1%	5%	0%	100%
CAT6		69%	2%	5%	4%	2%	5%	9%	3%	0%	100%
CAT7		71%	2%	5%	2%	7%	2%	3%	7%	0%	100%
CAT8		5%	0%	0%	10%	1%	5%	75%	4%	0%	100%
Total		57%	2%	8%	7%	5%	6%	11%	3%	0%	100%

[swuehl]

Try

sum(Amt) / sum(total<Category> Amt)

[amweiner · Accepted solution]

That did not work.

[amweiner]

I am attaching a qvw file that contains the data and illustrates the issue.

[#5]

Please find attached qvw..

[amweiner]

I tried the original solution:

(Amt)/sum(total<Category> Amt)

And it is working now. I don;t know what happened the first time I tried, though.