Cumulative Sum for Date Range

[microwin88x]

Hello,

I have the following table:

(Date has format: DD/MM/YYYY)

DATE	VALUE	AUX_DATE	CUMULATIVE
01/01/2017	50	22/12/2016
02/01/2017	70	23/12/2016
03/01/2017	10	24/12/2016
04/01/2017	120	25/12/2016
05/01/2017	10	26/12/2016
06/01/2017	10	27/12/2016
07/01/2017	10	28/12/2016
08/01/2017	10	29/12/2016
09/01/2017	10	30/12/2016
10/01/2017	10	31/12/2016
11/01/2017	10	01/01/2017
12/01/2017	10	02/01/2017
13/01/2017	10	03/01/2017
14/01/2017	10	04/01/2017
15/01/2017	20	05/01/2017	100
16/01/2017	150	06/01/2017	110
17/01/2017	115	07/01/2017	250
18/01/2017	10	08/01/2017	355
19/01/2017	70	09/01/2017	355
20/01/2017	80	10/01/2017	415
21/01/2017	60	11/01/2017	485
22/01/2017	30	12/01/2017	535
23/01/2017	90	13/01/2017	555

What I need to do is to calculate the column CUMULATIVE. For example:

The row with DATE = 15/01/2017 and AUX_DATE = 05/01/2017 has CUMULATIVE = 100

The CUMULATIVE should be the SUM of VALUE from AUX_DATE (05/01/2017) to DATE-1 (14/01/2017)

DATE	VALUE	AUX_DATE
05/01/2017	10	26/12/2016
06/01/2017	10	27/12/2016
07/01/2017	10	28/12/2016
08/01/2017	10	29/12/2016
09/01/2017	10	30/12/2016
10/01/2017	10	31/12/2016
11/01/2017	10	01/01/2017
12/01/2017	10	02/01/2017
13/01/2017	10	03/01/2017
14/01/2017	10	04/01/2017

The row with DATE = 16/01/2017 and AUX_DATE = 06/01/2017 has CUMULATIVE = 110

The CUMULATIVE should be the SUM of VALUE from AUX_DATE (06/01/2017) to DATE-1 (15/01/2017)

DATE	VALUE	AUX_DATE
06/01/2017	10	27/12/2016
07/01/2017	10	28/12/2016
08/01/2017	10	29/12/2016
09/01/2017	10	30/12/2016
10/01/2017	10	31/12/2016
11/01/2017	10	01/01/2017
12/01/2017	10	02/01/2017
13/01/2017	10	03/01/2017
14/01/2017	10	04/01/2017
15/01/2017	20	05/01/2017

Do you know how could I do this on Script?

Thank you!!!

[maxgro · Accepted solution]

in image the result, look at the attachment

A:

LOAD DATE,

     VALUE,

     AUX_DATE

     //CUMULATIVE

FROM

[https://community.qlik.com/thread/244933]

(html, codepage is 1252, embedded labels, table is @1);

B:

load

  DATE,

  Date(AUX_DATE + IterNo() -1) as DATE2

Resident A

While Date(AUX_DATE + IterNo() -1) <= (DATE -1);

Left Join (B)

LOAD

  DATE as DATE2,

  VALUE as VALUE2

Resident A;

Left join (A)

LOAD DATE, sum(VALUE2) as CUMULATIVE

Resident B

Group By DATE;

// you can drop table B here