Percentage with AGGR function

[rudywelvaert]

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.

[Miguel_Angel_Baeyens]

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).

[sunny_talwar]

Try this

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

[rudywelvaert]

Thanks Miguel

[rudywelvaert]

Thanks Sunny

[Miguel_Angel_Baeyens]

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