Report Summary Query

[bdunphy]

Hi,

I have a table with  the following output

Customer   Name	Product Code	Item name	Actual	Forecast	Variance	%
CUST A	1	Product 1	0	249	249	0%
CUST A	2	Product 2	21	41	20	51%
CUST A	3	Product 3	378	390	12	97%
CUST A	4	Product 4	316	202	-114	157%
CUST A	5	Product 5	112	115	3	97%
CUST A	6	Product 6	84	115	31	73%
CUST A	7	Product 7	42	48	6	87%
CUST A	8	Product 8	82	37	-45	220%
CUST A	9	Product 9	126	160	34	79%
CUST A	10	Product 10	48	259	211	19%
CUST A	11	Product 11	1,064	830	-234	128%
CUST A	12	Product 12	76	74	-2	102%
CUST A	13	Product 13	9	29	20	31%
CUST A	14	Product 14	0	13	13	0%
CUST A	15	Product 15	7,300	1,762	-5,538	414%
CUST A	Total		9,658	4,325	-5,333	223%

What I would like to  do is summarise this output as below:

15  Products:

Bucket | No of  Products | % of Products

+/- 10% | 3 |  20%

+/- 20% | 2 |  13%

+/- 30% | 3 |  20%

>30% |7 |  47%

NOTE: +/- 10% means the  forecast was between 90 and 110 % of the Actual, +/- 20% means between 80-89 and  111-120 etc.

This will allow  management to see how many products are close enough to forecast (within 30%)

so in the example  above 8 products are within 30% out of the 15 or 53% of  products

I have been trying  various combinations of aggr and count (and class) but cant get it to  work

The calculation for  the % figure is

sum

({$<Year={$(=max(Year)) },[Forecast  Available]={'Yes'}>}Sales.Unit2*[Packs per  Case])
/
sum({$<Year={$(=max(Year)) },[Forecast  Available]={'Yes'}>}Forecast_Packs)

Thanks

Brian

[bdunphy]

3 columns missing at end of spreadsheet during post. see below:

Forecast	Variance	%
249	249	0%
41	20	51%
390	12	97%
202	-114	157%
115	3	97%
115	31	73%
48	6	87%
37	-45	220%
160	34	79%
259	211	19%
830	-234	128%
74	-2	102%
29	20	31%
13	13	0%
1,762	-5,538	414%
4,325	-5,333	223%