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

    Report Summary Query

    Brian Dunphy
      Hi,
      I have a table with  the following output
      Customer   Name
      Product CodeItem nameActualForecastVariance%
      CUST A1Product 102492490%
      CUST A2Product 221412051%
      CUST A3Product 33783901297%
      CUST A4Product 4316202-114157%
      CUST A5Product 5112115397%
      CUST A6Product 6841153173%
      CUST A7Product 74248687%
      CUST A8Product 88237-45220%
      CUST A9Product 91261603479%
      CUST A10Product 104825921119%
      CUST A11Product 111,064830-234128%
      CUST A12Product 127674-2102%
      CUST A13Product 139292031%
      CUST A14Product 14013130%
      CUST A15Product 157,3001,762-5,538414%
      CUST ATotal9,6584,325-5,333223%
      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