Recommended Purchases

[dmac1971]

Trying to work out how to show what percentage of my recommended spares list my dealers are purchasing?

Example Rec List :

Reccomended Spares
Family	Part No	Desc	Type	Car Qty	Rec Qty
Blue Car	AB1	A Part Desc	Engine	5	2
Red Car	AB2	A Part Desc	Wheels	7	3
Green Car	AB3	A Part Desc	Bodywork	1	1
Red Car	AB4	A Part Desc	Electrical	10	5
Blue Car	AB5	A Part Desc	Engine	22	15
Red Car	AB6	A Part Desc	Wheels	5	4
Green Car	AB7	A Part Desc	Bodywork	1	1
Red Car	AB8	A Part Desc	Electrical	2	1
Green Car	AB9	A Part Desc	Bodywork	1	1
Red Car	AB10	A Part Desc	Electrical	10	2
Blue Car	AB11	A Part Desc	Engine	40	10
Blue Car	AB12	A Part Desc	Bodywork	30	20
Red Car	AB13	A Part Desc	Electrical	5	2
Green Car	AB14	A Part Desc	Electrical	7	4

Example Sales List

Dealer	Date	Month	Year	Item	Qty
Qual Cars	22/11/2013	Nov	2013	AB4	1
GG Cars	05/10/2013	Oct	2013	AB8	2
Autoland	28/01/2013	Jan	2013	AB11	1
Just Drive	25/01/2013	Jan	2013	AB13	2
Donands	08/07/2013	Jul	2013	AB1	2
Qual Cars	22/12/2013	Dec	2013	AB9	1
GG Cars	20/07/2013	Jul	2013	AB7	1
Autoland	18/12/2013	Dec	2013	AB2	2
Autoland	18/06/2013	Jun	2013	AB14	10
Just Drive	01/02/2013	Feb	2013	AB3	1
Donands	01/03/2013	Mar	2013	AB10	9
Autoland	04/08/2013	Aug	2013	AB8	1
Just Drive	09/05/2013	May	2013	AB4	5
Donands	07/09/2013	Sep	2013	AB8	1
Qual Cars	25/07/2013	Jul	2013	AB11	15
GG Cars	09/04/2013	Apr	2013	AB13	5
Autoland	11/08/2013	Aug	2013	AB13	6
Autoland	16/07/2013	Jul	2013	AB1	1
Qual Cars	27/08/2013	Aug	2013	AB9	1
GG Cars	07/07/2013	Jul	2013	AB7	1
Autoland	15/08/2013	Aug	2013	AB2	2
Just Drive	13/10/2013	Oct	2013	AB14	12
Just Drive	20/08/2013	Aug	2013	AB3	2
Donands	28/08/2013	Aug	2013	AB10	6
Autoland	23/08/2013	Aug	2013	AB3	2
Donands	24/03/2013	Mar	2013	AB10	1
Qual Cars	21/09/2013	Sep	2013	AB8	5
GG Cars	29/09/2013	Sep	2013	AB4	3
Autoland	26/10/2013	Oct	2013	AB8	5
GG Cars	24/05/2013	May	2013	AB11	60
Autoland	03/10/2013	Oct	2013	AB13	1

Required Output :

Dealer	Part No	Bought Qty	Rec Qty	% Purchased
Autoland	AB1	1	2	50%
Autoland	AB11	1	10	10%
Autoland	AB13	7	2	350%
Autoland	AB14	10	4	250%
Autoland	AB2	4	3	133%
Autoland	AB3	2	1	200%
Autoland	AB8	6	1	600%
Donands	AB1	2	2	100%
Donands	AB10	16	2	800%
Donands	AB8	1	1	100%
GG Cars	AB11	60	10	600%
GG Cars	AB13	5	2	250%
GG Cars	AB4	3	5	60%
GG Cars	AB7	2	1	200%
GG Cars	AB8	2	1	200%
Just Drive	AB13	2	2	100%
Just Drive	AB14	12	4	300%
Just Drive	AB3	3	1	300%
Just Drive	AB4	5	5	100%
Qual Cars	AB11	15	10	150%
Qual Cars	AB4	1	5	20%
Qual Cars	AB8	5	1	500%
Qual Cars	AB9	2	1	200%

Any direction on this?

[MK_QSL]

Like This?

[maxgro]

It seems to me that if you rename [Part No] to Item you can use your two tables as they are and get the required output in a straigth table chart:

SCRIPT

[Example Rec List]:

LOAD Family,

     [Part No] as Item,

     Desc,

     Type,

     [Car Qty],

     [Rec Qty]

FROM

[http://community.qlik.com/thread/108643]

(html, codepage is 1252, embedded labels, table is @1, filters(

Remove(Row, Pos(Top, 1))

));

[Example Sales List]:

LOAD Dealer,

     Date,

     Month,

     Year,

     Item,

     Qty

FROM

[http://community.qlik.com/thread/108643]

(html, codepage is 1252, embedded labels, table is @2);

RESULT

expression are the last 3 cols

Sum ([Qty])

Sum ([Rec Qty])

column(1)/column(2)

Dealer	Part No	Bought Qty	Rec Qty	% Purchased
Autoland	AB1	1	2	50%
Autoland	AB2	4	3	133%
Autoland	AB3	2	1	200%
Autoland	AB8	6	1	600%
Autoland	AB11	1	10	10%
Autoland	AB13	7	2	350%
Autoland	AB14	10	4	250%
Donands	AB1	2	2	100%
Donands	AB8	1	1	100%
Donands	AB10	16	2	800%
GG Cars	AB4	3	5	60%
GG Cars	AB7	2	1	200%
GG Cars	AB8	2	1	200%
GG Cars	AB11	60	10	600%
GG Cars	AB13	5	2	250%
Just Drive	AB3	3	1	300%
Just Drive	AB4	5	5	100%
Just Drive	AB13	2	2	100%
Just Drive	AB14	12	4	300%
Qual Cars	AB4	1	5	20%
Qual Cars	AB8	5	1	500%
Qual Cars	AB9	2	1	200%
Qual Cars	AB11	15	10	150%