Cumulative sum by certain dimension

[#1]

Hi,

first of all, the file attached demonstrates the problem, so you're welcome to use it.

This is the data:

date	type	bad
1/1/2014	A	1
1/1/2014	B	5
2/1/2014	A	5
2/1/2014	B	7
3/1/2014	A	9
3/1/2014	B	22
4/1/2014	B	1
5/1/2014	A	5

I want to have a pivot table with a column that sums the count of `bad` of a certain type thus far, meaning it would look like this:

date	type	bad	Cumulative bad
1/1/2014	A	1	1
	B	5	5
2/1/2014	A	5	6
	B	7	12
3/1/2014	A	9	15
	B	22	34
4/1/2014	B	1	35
5/1/2014	A	5	20

I Tried to do the following:

rangesum(above(sum(countBad),0,RowNo()))

but it sums for each date, regardless of type.

Thanks!

[ashfaq_haseeb]

Hi,

Try like this

[Data]:
LOAD * INLINE [
    date, type, count, countBad
    1/1/2014, A, 100, 1
    1/1/2014, B, 100, 5
    2/1/2014, A, 100, 5
    2/1/2014, B, 100, 7
    3/1/2014, A, 100, 9
    3/1/2014, B, 100, 22
    4/1/2014, B, 100, 1
    5/1/2014, A, 100, 5
];






[Final]:  
LOAD  
  *,  
  RangeSum([countBad], Peek('Acc')) AS [Acc]  
 Resident [Data] Order By [type],[date] ASC;  


DROP Table [Data];  

Regards

ASHFAQ

[#3]

Thanks,  but it's not quite it.

It does work for type A, but it starts counting for type B from 20 (the last sum of type A):

[ashfaq_haseeb]

Would you like to reset for B or what is expected.

Regards

ASHFAQ

[#5]

Yes, I would like that for each type it would reset (there could be more than two)

[morganaaron]

Hi Amalin,

Try wrapping it in an Aggr statement so:

Aggr(RangeSum(Above(Sum(countBad),0,RowNo())),type,date)

[ashfaq_haseeb]

Try this

[Data]:
LOAD * INLINE [
    date, type, count, countBad
    1/1/2014, A, 100, 1
    1/1/2014, B, 100, 5
    2/1/2014, A, 100, 5
    2/1/2014, B, 100, 7
    3/1/2014, A, 100, 9
    3/1/2014, B, 100, 22
    4/1/2014, B, 100, 1
    5/1/2014, A, 100, 5
];



[Final]:  
LOAD  
  *,  IF([type] = Previous([type]), RangeSum([countBad], Peek('Acc')),[countBad]) AS [Acc]   
Resident [Data] Order By [type],[date] ASC;  


DROP Table [Data];

Let me know if that worked.

Rega

[simenkg]

Change it to:

if(Peek('type')=type,RangeSum([countBad], Peek('Acc')),[countBad]) AS [Acc]

[its_anandrjs]

Hi,

Try this way with the peek function help

Data:

LOAD * INLINE [

    date, type, count, countBad

    1/1/2014, A, 100, 1

    1/1/2014, B, 100, 5

    2/1/2014, A, 100, 5

    2/1/2014, B, 100, 7

    3/1/2014, A, 100, 9

    3/1/2014, B, 100, 22

    4/1/2014, B, 100, 1

    5/1/2014, A, 100, 5

];

Final:   

    LOAD    *,

       IF( type = Previous( type ), Peek('Accumulate') + countBad, countBad) AS [Accumulate]    

    Resident Data Order By type,date ASC;   

    DROP Table Data; 

And final output is

Regards

Anand