Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to find Range Sum

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;

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

3 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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

rubenmarin

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