AGGR function required for this pivot table?

[#1]

Hi guys,

I have created a pivot table on a much larger scale but similar to below:

service	name	C	W	?	01/07/10	02/07/10	03/07/10	04/07/10
A	W	21:00	20:00	?	19:45	20:30	21:40	21:25
A	X	95	90	?	60	70	80	80
A	Y	95	90	?	92	88	89	90
A	Z	95	90	?	75	79	84	50

The calculations behind the figures are quite complex as they have to take into account whether the figures are numeric of time values, also some of the warning values can be greater or smaller than the critical value etc. There is a Red, Amber, Green (RAG) status. From this I would like to create a RAG % which shows the ratio of Green or Amber cells for each name. So for Name X, the RAG % = 100% as everything is green. I can create the calculation in a simple text box but when i try to import this into a pivot table it always seems to create a row, rather than a column. I tried using the AGGR function as demonstrated below but it still didn't work?

sum(aggr(if($(vKPIRAGMin) = 'G' or $(vKPIRAGMin) = 'A', 1, 0),
KPIBusinessDate))/
sum(aggr(if($(vKPIRAGMin) = 'R' or $(vKPIRAGMin) = 'A' or $(vKPIRAGMin) = 'G' , 1, 0),
KPIBusinessDate))

KPIRAGMin is a variable I use to determine whether the cell is R, A or G. KPIBusinessDate is simply the date at the top of the table.

If anyone has any suggestions as to how I could get the ratio/RAG% into the column where there is the ? it would be much appreciated.

Regards,

[#2]

Just a quick bump to this as I'm still having trouble getting the figures. Not sure whether to try a completely new approach or stick with my current method.

Thanks,

[#3]

Hi guys,

Anyone have any suggestions at all how to combat this problem? A separate table would suffice which would gave a percentage of the number of Green or Amber Cells across each name.

The calculation itself works when i select one individual name but doesn't work correctly when all of the names are selected so I think when nothing is selected it doesn't look at each name individually.

I really would appreciate any comments/suggestions.

Thanks,