How to find Range Sum

[#1]

i want to get the result as

Product,Date,Amount,AccumlatedAmount

A,9/15/2015,20,20

A,9/16/2015,-,20

A,9/17/2015,-,20

A,9/18/2015,40,60

B,9/15/2015,-,0

B,9/16/2015,5,5

B,9/17/2015,-,5

B,9/18/2015,10,15

for the data given below i tried with below script but not getting the desired result

Data:

LOAD * INLINE [

    Product, Date, Amount

    A, 9/15/2015, 20

    A, 9/18/2015, 40

    B, 9/16/2015, 5

    B, 9/18/2015, 10

];

Join

Master:

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1<=MaxDate;

LOAD Min(FieldValue('Date',RecNo())) as MinDate,

     Max(FieldValue('Date',RecNo())) as MaxDate

AutoGenerate FieldValueCount('Date');

Range:

LOAD Product, Date, Amount,

            If( Product=Peek(Product),RangeSum(Amount,Peek(AccumulatedAmount)),RangeSum(Amount)) as AccumulatedAmount

Resident Data Order By Date,Product;

[Anonymous]

Hi,

Please Try:

Data:

LOAD Product,

Amount,

Date(Date#(Date,'MM/DD/YYYY'),'DD/MM/YYYY') as Date;

LOAD * INLINE [

    Product, Date, Amount

    A, 9/15/2015, 20

    A, 9/18/2015, 40

    B, 9/16/2015, 5

    B, 9/18/2015, 10

];

Left Join(Data)

LOAD Product,Date(MinDate+IterNo()-1) as Date

While  iterno()-1 <= MaxDate - MinDate;

LOAD Product,

     Min(Date) as MinDate,

     Max(Date) as MaxDate

Resident Data

Group By Product;

Range:

LOAD Product,Date, Amount,

            If( Product = Previous(Product),Rangesum(Amount,Peek(AccumulatedAmount)),Amount) as AccumulatedAmount

Resident Data Order By Product,Date ;

DROP Table Data;

Regards

Neetha

[#3]

still not getting 17th date and related records of 17th

[rubenmarin · Accepted solution]

Hi, in the script of neetha change "Left Join" to "Join" (wich by default is an Outer Join)