Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Average of 12 months, by line of data

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:

    

DateInvoiceIDLineItemCurrencyPriceAvgPrice??
15/02/201501710810317-HJF10R-K20EUR8.82
15/02/201501710820317-HJF10R-K20EUR9.03
15/02/201501710830317-HJF10R-K20EUR8.82
15/02/201501710840317-HJF10R-K20EUR8.82
15/02/201501710850317-HJF10R-K20EUR9.03
15/02/201501710860317-HJF10R-K20EUR8.82
15/02/201501710870317-HJF10R-K20EUR9.03
26/10/201401550520317-HJC08R-K20EUR8.96
01/09/201401479210317-HJC08R-K20EUR8.96
11/03/201401246320317-HJC08R-K20EUR8.96
12/12/201301116310745-2802NTXHUSD11.07
24/04/201300821710745-2802NTXHUSD0.00
17/03/201300775210745-2802NTXHUSD0.00
26/08/201401473820246-0001XHP1USD5.67
26/08/201401473810746-0001XHP1USD7.22
31/10/201200584960242-058802XH-M1USD12.15
28/08/201200506910745-2802NTXHUSD10.75
14/03/201200240750745-2802NTXHUSD8.95
02/11/201401561810745-0001XHUSD7.22
12/12/201301116420247-09SA-HUSD8.63
31/10/201200584990251-0504XHUSD24.72
11/10/201200551620745-2802BTXHUSD11.07
12/12/201301116410247-0904GNXJUSD17.15
11/10/201200551610745-0002BPXHUSD15.66
28/08/201200506920745-0002BPXHUSD15.66

Hope you can help me with the script

Thanks a head,

Dima

1 Reply
Highlighted
MVP
MVP

You can use peek() like:

Load

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