Keeping denominator per grouping the same in a pivot table in order to report percentage of each group

[neverbuyit]

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%

[MayilVahanan]

Hi @neverbuyit 

Try like below

Sum(Total<DeptDim>CaseTicket)

or

Sum(Total<DeptDim, Year-Month>CaseTicket)

[neverbuyit]

Thanks for the response Mayil. However as mentioned i seem to run into an issue when using the TOTAL scope qualifier in this case. For instance using your suggestion and applying it to the sample tables above:

COUNT(DISTINCT TOTAL <Dept> RecID) or COUNT(DISTINCT TOTAL <Dept, Status> RecID) or COUNT(DISTINCT TOTAL <Dept, Status, Year-Month> RecID) all seem to give the total number of RecIDs which is 191,865 (sum of the "Totals" in the raw numbers table). I need the total to be at the department level, so that each status is percentage of the total per department, rather than of all possible values