- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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):
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Would you like to reset for B or what is expected.
Regards
ASHFAQ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, I would like that for each type it would reset (there could be more than two)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Amalin,
Try wrapping it in an Aggr statement so:
Aggr(RangeSum(Above(Sum(countBad),0,RowNo())),type,date)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Change it to:
if(Peek('type')=type,RangeSum([countBad], Peek('Acc')),[countBad]) AS [Acc]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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