Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative sum by certain dimension

Hi,

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

This is the data:

datetypebad
1/1/2014A1
1/1/2014B5
2/1/2014A5
2/1/2014B7
3/1/2014A9
3/1/2014B22
4/1/2014B1
5/1/2014A5

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:

datetypebadCumulative bad
1/1/2014A11
B55
2/1/2014A56
B712
3/1/2014A915
B2234
4/1/2014B135
5/1/2014A520

I Tried to do the following:

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

but it sums for each date, regardless of type.

Thanks!

8 Replies
ashfaq_haseeb
Champion III
Champion III

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

Not applicable
Author

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):

Capture.PNG.png

ashfaq_haseeb
Champion III
Champion III

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

Regards

ASHFAQ

Not applicable
Author

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

morganaaron
Specialist
Specialist

Hi Amalin,

Try wrapping it in an Aggr statement so:

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

ashfaq_haseeb
Champion III
Champion III

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
Specialist
Specialist

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

op3.png

Regards

Anand