topic Re: How to find Range Sum in QlikView

How to find Range Sum

Tue, 06 Oct 2015 07:05:18 GMT

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;

Re: How to find Range Sum

Anonymous — Tue, 06 Oct 2015 08:33:36 GMT

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

Re: How to find Range Sum

Tue, 06 Oct 2015 09:47:18 GMT

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

Re: How to find Range Sum

rubenmarin — Tue, 06 Oct 2015 09:57:23 GMT

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