1 Reply Latest reply: Oct 13, 2011 1:44 PM by Brian Dunphy

# Report Summary Query

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
• ###### Report Summary Query

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%