Average of 12 months, by line of data

[dimak123]

Hi to all,

Hoping you can help me,

I have a table of invoices, for each line in the script, i need to calculate the avg.price of the item with the same currency for the past 12 month and not does not include the line.

Here is a sample of my data:

Date	InvoiceID	Line	Item	Currency	Price	AvgPrice??
15/02/2015	017108	1	0317-HJF10R-K20	EUR	8.82
15/02/2015	017108	2	0317-HJF10R-K20	EUR	9.03
15/02/2015	017108	3	0317-HJF10R-K20	EUR	8.82
15/02/2015	017108	4	0317-HJF10R-K20	EUR	8.82
15/02/2015	017108	5	0317-HJF10R-K20	EUR	9.03
15/02/2015	017108	6	0317-HJF10R-K20	EUR	8.82
15/02/2015	017108	7	0317-HJF10R-K20	EUR	9.03
26/10/2014	015505	2	0317-HJC08R-K20	EUR	8.96
01/09/2014	014792	1	0317-HJC08R-K20	EUR	8.96
11/03/2014	012463	2	0317-HJC08R-K20	EUR	8.96
12/12/2013	011163	1	0745-2802NTXH	USD	11.07
24/04/2013	008217	1	0745-2802NTXH	USD	0.00
17/03/2013	007752	1	0745-2802NTXH	USD	0.00
26/08/2014	014738	2	0246-0001XHP1	USD	5.67
26/08/2014	014738	1	0746-0001XHP1	USD	7.22
31/10/2012	005849	6	0242-058802XH-M1	USD	12.15
28/08/2012	005069	1	0745-2802NTXH	USD	10.75
14/03/2012	002407	5	0745-2802NTXH	USD	8.95
02/11/2014	015618	1	0745-0001XH	USD	7.22
12/12/2013	011164	2	0247-09SA-H	USD	8.63
31/10/2012	005849	9	0251-0504XH	USD	24.72
11/10/2012	005516	2	0745-2802BTXH	USD	11.07
12/12/2013	011164	1	0247-0904GNXJ	USD	17.15
11/10/2012	005516	1	0745-0002BPXH	USD	15.66
28/08/2012	005069	2	0745-0002BPXH	USD	15.66

Hope you can help me with the script

Thanks a head,

Dima

[tresesco]

You can use peek() like:

Load

          RangeAvg(Peek('Price'), Peek('Price', -2), .....Peek('Price', -12)) as AvgPrice