Report Summary Query

bdunphy — Thu, 13 Oct 2011 15:03:49 GMT

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

bdunphy — Thu, 13 Oct 2011 17:44:01 GMT

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%

topic Report Summary Query in QlikView

Report Summary Query

Report Summary Query