Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

bdunphy
New Contributor III

Report Summary Query

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
Tags (3)
1 Reply
bdunphy
New Contributor III

Report Summary Query

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

ForecastVariance%
2492490%
412051%
3901297%
202-114157%
115397%
1153173%
48687%
37-45220%
1603479%
25921119%
830-234128%
74-2102%
292031%
13130%
1,762-5,538414%
4,325-5,333223%
Community Browser