Cummulative Calculation based on condition

[#1]

Hi All,

I am stuck in one of the requirement and I am not able to find the right solution.

ID	Seq	Category	Amount
1	1	a	100
1	2	a	200
1	3	a	300
1	4	b	200
1	5	c	150
2	6	c	300
2	7	a	100
2	8	b	100
2	9	b	300
2	10	a	175

I need output like below

ID	Seq	Category	Amount	Cummulative
1	1	a	100	100
1	2	a	200	200
1	3	a	300	300
1	4	b	200	400	“(100+200+300)-200”
1	5	c	150	850	“(100+200+300+400)-150”
2	6	c	300	300
2	7	a	100	2050	“(100+200+300+400+850+300)-100”
2	8	b	100	4400
2	9	b	300	300
2	10	a	175	8725

So basically when Category is Same, amount will be same, but when category changed sequentially (eg. Seq=4) , amount should be calculated as (sum of all previous category amount - current category amount)

Hope I have explained the logic well.

gwassenaar  swuehl‌ sunindia‌ kush141087‌

‌

[perumal_41]

alternative solution

Temp1:
LOAD * INLINE [
ID, Seq, Category, Amount
1, 1, a, 100
1, 2, a, 200
1, 3, a, 300
1, 4, b, 200
1, 5, c, 150
2, 6, c, 300
2, 7, a, 100
2, 8, b, 100
2, 9, b, 300
2, 10, a, 175
];

Temp2:
Load * ,if(RowNo()=1,Amount,if (Category=Peek(Category), Peek(Cum)+Amount,Peek(Cum)+Peek(Cum)-Amount)) as Cum,if(RowNo()<>1 and Category <> peek(Category), Amount,0) as Deu Resident  Temp1;


Load *,if(RowNo()=1,Cum,if(Category<>Peek(Category),Cum-Peek(Cum),Amount)) as Cum1 Resident Temp2;

DROP Table Temp1,Temp2;

[#3]

thanks all.. and specially qlik superhero swuehl‌