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
