Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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):
Would you like to reset for B or what is expected.
Regards
ASHFAQ
Yes, I would like that for each type it would reset (there could be more than two)
Hi Amalin,
Try wrapping it in an Aggr statement so:
Aggr(RangeSum(Above(Sum(countBad),0,RowNo())),type,date)
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
Change it to:
if(Peek('type')=type,RangeSum([countBad], Peek('Acc')),[countBad]) AS [Acc]
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