Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

Tags (1)
1 Reply
MVP
MVP

Re: Average of 12 months, by line of data

You can use peek() like:

Load

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