topic Re: Aggregate multiple dimensions in rangesum expression in QlikView

Aggregate multiple dimensions in rangesum expression

haribabugv — Sat, 16 Nov 2024 04:11:40 GMT

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

Re: Aggregate multiple dimensions in rangesum expression

tresB — Wed, 13 Mar 2019 08:52:36 GMT

Try this:

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

Re: Aggregate multiple dimensions in rangesum expression

shiveshsingh — Wed, 13 Mar 2019 09:01:42 GMT

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;

Re: Aggregate multiple dimensions in rangesum expression

haribabugv — Wed, 13 Mar 2019 10:13:03 GMT

Hey Shivesh,

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

Re: Aggregate multiple dimensions in rangesum expression

haribabugv — Wed, 13 Mar 2019 10:14:45 GMT

Hello Tresesco,

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