Hello,
Was wondering if anyone knows what I can do in the Qlik Sense pivot table so that the denominator remains consistent for THAT GROUP and can therefore report the percentages of each accordingly. I've been having some trouble trying to figure this out and perhaps missing some piece of the formula. Using the TOTAL and ALL modifiers or their set analysis equivalents (e.g. {1}) is not what's needed as I don't want the denominator to be the entire data subset, just those in each grouping.
See the sample tables below. The first lists the raw numbers and the second converts it to its percentage. For instance, in the month of October, Status 2 for Dept 1 is 199/915 or 21.75%. Status 4 for same department is 80/915 or 8.74%
If anyone can provide a QVF with this sample data that renders the raw #'s and percentages below that would be ideal, but formula for reporting the percentages as shown in the second table below would be great too.
Also attached the excel of the sample for reference
Raw Numbers
| Year-Month | 2020-Oct |
| | Totals | Status 1 | Status 2 | Status 3 | Status 4 | Status 5 |
| Dept 1 | 915 | 629 | 199 | 4 | 80 | 3 |
| Dept 2 | 9,277 | 6,785 | 2,157 | 2 | 300 | 33 |
| Dept 3 | 2,031 | 1,471 | 493 | 12 | 53 | 2 |
| Dept 4 | 0 | 0 | 0 | 0 | 0 | 0 |
| Dept 5 | 0 | 0 | 0 | - | 0 | 0 |
| Dept 6 | 5,339 | 3,876 | 1,306 | 9 | 142 | 6 |
| Dept 7 | 56,511 | 36,863 | 17,152 | 109 | 2,244 | 142 |
| Dept 8 | 0 | 0 | 0 | - | 0 | 0 |
| Dept 9 | 1,329 | 878 | 279 | 19 | 135 | 18 |
| Dept 10 | 10,077 | 6,176 | 3,219 | 74 | 563 | 45 |
| Dept 11 | 1,579 | 1,129 | 413 | 8 | 25 | 4 |
| Dept 12 | 1,113 | 769 | 269 | 2 | 72 | 1 |
| Dept 13 | 24,648 | 17,672 | 5,796 | 42 | 1,075 | 63 |
| Dept 14 | 7,360 | 4,578 | 2,536 | 2 | 242 | 2 |
| Dept 15 | 13,523 | 8,897 | 3,917 | 37 | 632 | 40 |
| Dept 16 | 6,601 | 4,186 | 2,001 | 3 | 404 | 7 |
| Dept 17 | 11,804 | 8,148 | 3,008 | 22 | 614 | 12 |
| Dept 18 | 10,121 | 8,146 | 1,680 | 12 | 233 | 50 |
| Dept 19 | 0 | 0 | 0 | - | 0 | 0 |
| Dept 20 | 4,158 | 2,942 | 1,069 | - | 130 | 17 |
| Dept 21 | 4,066 | 2,572 | 1,244 | 3 | 230 | 17 |
| Dept 22 | 14,207 | 9,596 | 3,771 | 39 | 764 | 37 |
| Dept 23 | 7,206 | 4,484 | 2,048 | 68 | 565 | 41 |
Percentages
| Year-Month | 2020-Oct |
| | Status 1 | Status 2 | Status 3 | Status 4 | Status 5 |
| Dept 1 | 68.74% | 21.75% | 0.44% | 8.74% | 0.33% |
| Dept 2 | 73.14% | 23.25% | 0.02% | 3.23% | 0.36% |
| Dept 3 | 72.43% | 24.27% | 0.59% | 2.61% | 0.10% |
| Dept 4 | - | - | - | - | - |
| Dept 5 | - | - | - | - | - |
| Dept 6 | 72.60% | 24.46% | 0.17% | 2.66% | 0.11% |
| Dept 7 | 65.23% | 30.35% | 0.19% | 3.97% | 0.25% |
| Dept 8 | - | - | - | - | - |
| Dept 9 | 66.06% | 20.99% | 1.43% | 10.16% | 1.35% |
| Dept 10 | 61.29% | 31.94% | 0.73% | 5.59% | 0.45% |
| Dept 11 | 71.50% | 26.16% | 0.51% | 1.58% | 0.25% |
| Dept 12 | 69.09% | 24.17% | 0.18% | 6.47% | 0.09% |
| Dept 13 | 71.70% | 23.52% | 0.17% | 4.36% | 0.26% |
| Dept 14 | 62.20% | 34.46% | 0.03% | 3.29% | 0.03% |
| Dept 15 | 65.79% | 28.97% | 0.27% | 4.67% | 0.30% |
| Dept 16 | 63.41% | 30.31% | 0.05% | 6.12% | 0.11% |
| Dept 17 | 69.03% | 25.48% | 0.19% | 5.20% | 0.10% |
| Dept 18 | 80.49% | 16.60% | 0.12% | 2.30% | 0.49% |
| Dept 19 | - | - | - | - | - |
| Dept 20 | 70.76% | 25.71% | - | 3.13% | 0.41% |
| Dept 21 | 63.26% | 30.60% | 0.07% | 5.66% | 0.42% |
| Dept 22 | 67.54% | 26.54% | 0.27% | 5.38% | 0.26% |
| Dept 23 | 62.23% | 28.42% | 0.94% | 7.84% | 0.57% |
