Aggregate multiple dimensions in rangesum expression

[haribabugv]

Hello Users,

Can you please check what is wrong in my expression . I am trying to calculate column called Balance in Qlikview as shown in excel.

sum(aggr(If(Rowno()>1,RangeSum(above([Balance])-sum(Plan)
,0,NoOfRows())),sum(Opening)-sum(Plan),Date,Material)) 

Material	Date	Plan	Opening	Balance
A	10-03-2019	13	64	51
A	11-03-2019	16		35
A	12-03-2019	14		21
A	13-03-2019	16		5
A	14-03-2019	11		-6
B	10-03-2019	12	32	20
B	11-03-2019	20		0
B	12-03-2019	12		-12
B	13-03-2019	19		-31
B	14-03-2019	13		-44

[tresesco]

Try this:

If(Sum(Opening)=0,above([Balance])-sum(Plan),sum(Opening)-sum(Plan))

[shiveshsingh]

Try this

T:LOAD * INLINE [
Material, Date, Plan, Opening
A, 10-03-2019, 13, 64
A, 11-03-2019, 16,
A, 12-03-2019, 14,
A, 13-03-2019, 16,
A, 14-03-2019, 11,
B, 10-03-2019, 12, 32
B, 11-03-2019, 20,
B, 12-03-2019, 12,
B, 13-03-2019, 19,
B, 14-03-2019, 13,

];
F:
load if(RowNo()=1,Opening - Plan, if(Material = Peek(Material), Peek(Balance)-Plan, Opening-Plan )) as Balance,*

Resident T;

drop table T;

[haribabugv]

Hey Shivesh, 

Thanks . Is there any way i can do that in chart by creating expression?

[haribabugv]

Hello Tresesco,

There are cases where Opening can be zero even for the 1st record. SO this solution may not work