topic Sum 3 past months (into script) in QlikView

Sum 3 past months (into script)

Sat, 24 Oct 2009 00:12:58 GMT

Product	Date	Sales
Product A	1/1/2008	200
Product A	2/1/2008	35
Product A	3/1/2008	562
Product A	4/1/2008	5562
Product A	5/1/2008	5423
Product A	6/1/2008	32
Product A	7/1/2008	32
Product A	8/1/2008	466
Product A	9/1/2008	216
Product A	10/1/2008	129
Product A	11/1/2008	2654
Product A	12/1/2008	265
Product B	1/1/2008	3235
Product B	2/1/2008	2362
Product B	3/1/2008	232
Product B	4/1/2008	454
Product B	5/1/2008	569
Product B	6/1/2008	57
Product B	7/1/2008	4
Product B	8/1/2008	489
Product B	9/1/2008	5489
Product B	10/1/2008	5121

I need to run a script in order to accumulate subtotal for the last 3 monhts, like this:

Product	Date	Sales
Product A	1/1/2008	200		Sum Jan+Dec(2007)+Nov(2007)
Product A	2/1/2008	35	6159
Product A	3/1/2008	562	11547
Product A	4/1/2008	5562	11017
Product A	5/1/2008	5423	5487
Product A	6/1/2008	32	530
Product A	7/1/2008	32	714
Product A	8/1/2008	466	811	Sum Aug+Jul+Jun (3month acculumation)
Product A	9/1/2008	216	2999	Sum Sep +Aug+Jul (3monht accumulation)

-------------------------------------------------------------------------------------------------------------------------

LOAD Product,
Date,
Year(Date) as Year,
Month(Date) as Month,
date(Date, 'MMM-YYYY' ) as MonthYear,
MonthName(Date) as MonthName,
Sales
FROM
//I need to run a script in order to accumulate subtotal for the last 3 monhts, like this:
//Jan2008 would be the SUM of Jan2008+Dec2007+Nov2007
//Feb2008 '' '' '' '' '' Feb2008+Jan2008+Dec2007
//Note that I have 2 different products

QVD file attached.

THANKS

Sum 3 past months (into script)

GabrielAraya — Sat, 24 Oct 2009 00:54:16 GMT

Hi, Did you test the Previous function?

Directory;

Test:

LOAD

Product ,

Date

Amount

FROM

Prueba.xlsx

(ooxml, embedded labels, table is Sheet1);

Final:

Load

Product

Date

Amount

(Product = Previous(Product),

RangeSum

(Amount,Previous(Amount),Previous(Previous(Amount))), Amount) as Acc

Resident

Drop

Table

Test;

Test;