Running balance BY ID

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Dear QLikers,

In need to calculate the running balance by ID , Below is an example of the output needed:

CUSTOMER_ID	Amount	Running Balance
1	158,393	158,393
1	1,000,000	1,158,393
1	(400,000)	758,393
1	(100,000)	658,393
1	(68,000)	590,393
1	(2,000)	588,393
1	500,000	1,088,393
1	(279,000)	809,393
1	(877,000)	(67,607)
1	(10,000)	(77,607)
1	100,000	22,393
1	(134)	22,258
2	6,207,334	6,207,334
2	3,560,000	9,767,334
2	5,600,000	15,367,334
2	(1,651)	15,365,682
2	(11,500,000)	3,865,682
2	(1,000)	3,864,682
2	(3,860,000)	4,682
2	(1,000)	3,682
2	6,800,000	6,803,682
2	1,318,000	8,121,682
2	(750,000)	7,371,682
2	7,093,000	14,464,682
2	(1,000)	14,463,682
2	(14,460,000)	3,682
2	(1,000)	2,682

Is it possible to do it in the script? and I need the expression using set analysis incase in case I need to calculated using straight table.

Thank you in advance.

Mario

Re: Running balance BY ID

Taoufiq_Zarra — Mon, 06 Jul 2020 12:26:51 GMT

Hi ,

One solution :

if(rowno()=1 or CUSTOMER_ID<>peek(CUSTOMER_ID),Amount,peek([Running Balance])+Amount) as [Running Balance]

example :

LOAD *,if(rowno()=1 or CUSTOMER_ID<>peek(CUSTOMER_ID),Amount,peek([Running Balance])+Amount) as [Running Balance] INLINE [ CUSTOMER_ID, Amount 1, 158393 1, 1000000 1, -400000 1, -100000 1, -68000 1, -2000 1, 500000 1, -279000 1, -877000 1, -10000 1, 100000 1, -134 2, 6207334 2, 3560000 2, 5600000 2, -1651 2, -11500000 2, -1000 2, -3860000 2, -1000 2, 6800000 2, 1318000 2, -750000 2, 7093000 2, -1000 2, -14460000 2, -1000 ];

output :

topic Re: Running balance BY ID in QlikView

Running balance BY ID

Re: Running balance BY ID