topic Re: Cumulative sum by certain dimension in QlikView

Cumulative sum by certain dimension

Wed, 26 Nov 2014 09:39:29 GMT

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
1/1/2014	B	5	5
2/1/2014	A	5	6
2/1/2014	B	7	12
3/1/2014	A	9	15
3/1/2014	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!

Re: Cumulative sum by certain dimension

ashfaq_haseeb — Wed, 26 Nov 2014 09:53:10 GMT

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

Re: Cumulative sum by certain dimension

Wed, 26 Nov 2014 11:09:10 GMT

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

Re: Cumulative sum by certain dimension

ashfaq_haseeb — Wed, 26 Nov 2014 11:11:16 GMT

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

Regards

ASHFAQ

Re: Cumulative sum by certain dimension

Wed, 26 Nov 2014 11:13:28 GMT

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

Re: Cumulative sum by certain dimension

morganaaron — Wed, 26 Nov 2014 11:26:55 GMT

Hi Amalin,

Try wrapping it in an Aggr statement so:

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

Re: Re: Cumulative sum by certain dimension

ashfaq_haseeb — Wed, 26 Nov 2014 11:28:06 GMT

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

Re: Cumulative sum by certain dimension

simenkg — Wed, 26 Nov 2014 11:29:21 GMT

Change it to:

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

Re: Re: Cumulative sum by certain dimension

its_anandrjs — Wed, 26 Nov 2014 11:36:27 GMT

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