topic Re: % of totals - Pivot table in App Development

% of totals - Pivot table

sinhanima — Sat, 16 Nov 2024 17:35:35 GMT

I have following table in Qlik, and i want to get % of Total based Monthly column totals for each Area of focus (yellow).

The formula i have right now is = sum(Missed Lines)/sum(<total> Missed Lines), but it is giving me % based entire table's total value. How can i acheive % based on Total values of Columns.

Year	2021
Fiscal Month	OCT		NOV		DEC
Area of Focus	Missed Lines	% Of Total Missed Lines	Missed Lines	% Of Total Missed Lines	Missed Lines	% Of Total Missed Lines
Totals	972	100.00%	639	100.00%	695	100.00%
A	107	11.01%	48	7.51%	30	4.32%
B	339	34.88%	300	46.95%	335	48.20%
C	86	8.85%	47	7.36%	45	6.47%
D	228	23.46%	107	16.74%	154	22.16%
E	212	21.81%	137	21.44%	131	18.85%

Re: % of totals - Pivot table

Ksrinivasan — Thu, 21 Jan 2021 17:19:09 GMT

hi,

find the example with DIM and Measures

ksrinivasan

Re: % of totals - Pivot table

sinhanima — Thu, 21 Jan 2021 18:19:33 GMT

Thanks Ksrinivasan, I have used exact same formula but this formula works when i don’t have months and years in columns. How can I make it work when Months/years or any other dimensions are used as column dimensions?

Re: % of totals - Pivot table

Ksrinivasan — Thu, 21 Jan 2021 18:47:46 GMT

Hi sinhanima,

my dummy data

table with %

ksrinivasan

Re: % of totals - Pivot table

sinhanima — Fri, 22 Jan 2021 08:56:37 GMT

For this solution i will need to call out (use set expressions) for each Cat as different measures, and if I introduce months or years or weeks in the columns, this solution will create multiple columns ( ex: 12 months X 5 different CAT X 2 different measures).

also as the number of CAT increases or data gets added for more number of months/years this will only create endless number of columns.