topic Re: Percentage with AGGR function in QlikView

Percentage with AGGR function

rudywelvaert — Mon, 31 Jul 2017 14:18:20 GMT

Hi,

I have a table with 3 columns: Month,Group,Number

If I make a pivot table with Month, Group and Sum(Number) I get this:

Counting
Month	Group	A	B	C	Total
1		39	20	0	59
2		0	37	11	48
3		39	57	18	114
4		9	36	25	70
5		28	36	31	95
6		19	36	21	76
7		25	45	26	96
8		27	13	22	62
9		19	1	17	37
10		30	14	33	77
11		20	19	7	46
12		27	51	19	97
Total		282	365	230	877

But I want a percentage table where every value Sum(Number) is divided by the total per month

e.g. Month = 1, Group = 1 -> 39/59 =0.6610 = 66.10%

So the table I get is thus:

Counting Percentage
Month	Group	A	B	C	Total
1		66,10%	33,90%	0,00%	100,00%
2		0,00%	77,08%	22,92%	100,00%
3		34,21%	50,00%	15,79%	100,00%
4		12,86%	51,43%	35,71%	100,00%
5		29,47%	37,89%	32,63%	100,00%
6		25,00%	47,37%	27,63%	100,00%
7		26,04%	46,88%	27,08%	100,00%
8		43,55%	20,97%	35,48%	100,00%
9		51,35%	2,70%	45,95%	100,00%
10		38,96%	18,18%	42,86%	100,00%
11		43,48%	41,30%	15,22%	100,00%
12		27,84%	52,58%	19,59%	100,00%
Total		32,16%	41,62%	26,23%	100,00%

Probably the Aggr function should be used for this, but how?

Anybody who has an idea.

Included Aggr.qvw with the data and two pivot tables: one with the sum and one where I would like the percentages

Thanks

R.W.

Re: Percentage with AGGR function

Miguel_Angel_Baeyens — Mon, 31 Jul 2017 14:23:42 GMT

Looks good, you can also simplify it using

Sum(TOTAL <Month> Number)

instead of using Aggr(). What is wrong there? The lack of 0%? If you don't have data it's a null and it will not show a 0 anyway (unless you create new data in the script).

Re: Percentage with AGGR function

sunny_talwar — Mon, 31 Jul 2017 14:27:11 GMT

Try this

=Sum(Number)/Sum(TOTAL <Month> Number)

Re: Percentage with AGGR function

rudywelvaert — Mon, 31 Jul 2017 14:27:46 GMT

Thanks Miguel

Re: Percentage with AGGR function

rudywelvaert — Mon, 31 Jul 2017 14:28:05 GMT

Thanks Sunny

Re: Percentage with AGGR function

Miguel_Angel_Baeyens — Mon, 31 Jul 2017 14:29:17 GMT

By the way, if you want to display the zeroes, go to the chart properties > Presentation and uncheck "Suppress Zero-Values"