Problem with average calculation

Tue, 30 Oct 2012 10:34:54 GMT

I have this situation:

SALES

Key	Key subsidiary+article	Subsidiary	DateMov	Client	Article	Amount
12009999	milanabc	milan	10/10/2012	1234	abc	120
	romedfg	rome	10/08/2012	9876	dfg	130
12007777	naplejkl	naple	10/06/2012	4567	jkl	200
	genoawer	genoa	10/04/2012	8901	wer	350

COST

Key	Subsidiary	DatePurch	Article	Cost
12009999	milan	10/05/2012	abc	100
12007777	naple	10/03/2012	jkl	150
12005555	genoa	10/03/2012	wer	250
12008980	rome	10/09/2012	dfg	95
12003875	rome	09/10/2012	dfg	100
12002698	naple	09/29/2012	jkl	120
12006945	milan	10/01/2012	abc	105
12001856	genoa	09/15/2012	wer	200

COST_NO_KEY

Key subsidiary+article	Subsidiary_nk	DatePurch_nk	Article_nk	Cost_nk
milanabc	milan	10/01/2012	abc	105
milanabc	milan	10/05/2012	abc	100
romedfg	rome	09/10/2012	dfg	100
romedfg	rome	10/09/2012	dfg	95
naplejkl	naple	09/29/2012	jkl	120
naplejkl	naple	10/03/2012	jkl	150
genoawer	genoa	09/15/2012	wer	200
genoawer	genoa	10/03/2012	wer	250

I have to calculate marginality for every single sales.

The problem is that not all sales are linked to cost, so i have created a second cost table with a created key that connect the single sales to every purchase done by subsidiary for a specific article.

I need to calculate an average cost of the last 10 days before sales. And in case in the last 10 days there's no purchase calculate an average cost of the last three purchases available before sale.

I use this function to calculate the 10 day average cost

avg(if(datepurch_nk>=date(datemov-10) and datepurch_nk<=datemov,cost_nk))

i have a function that calculate the first purchase before sales but i can't use this in other aggragation

max(if(datemov>=datepurch_nk,datepurch_nk))

COST_NO_KEY is ordered by subsidiary, article and date.

Can someone help me please?

Thanks

topic Problem with average calculation in QlikView

Problem with average calculation