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% |
