topic Re: Cumulative 12 months Sum in Load Script in QlikView

Cumulative 12 months Sum in Load Script

techvarun — Sat, 16 Nov 2024 21:18:03 GMT

Hi Experts

I have table like below and need to create a accumulated field as mentioned in the expected sum column.

How to achieve this effectively in the load script itself?

Date	Amount	Period	Expected Result
43374	10	1	10
43405	20	2	30
43435	30	3	60
43466	40	4	100
43497	50	5	150
43525	60	6	210
43556	70	7	280
43586	80	8	360
43617	90	9	450
43647	100	10	550
43678	110	11	660
43709	120	12	780

Many Thanks in advance

Re: Cumulative 12 months Sum in Load Script

pradosh_thakur — Wed, 06 Mar 2019 07:59:58 GMT

rangesum(above(sum(Amount),0,rowno()))

Re: Cumulative 12 months Sum in Load Script

albert_guito — Wed, 06 Mar 2019 08:02:18 GMT

You have to use a GROUP BY sentence in load script, but maybe is more flexible do that in a expression in the front end using set analysis

Re: Cumulative 12 months Sum in Load Script

techvarun — Wed, 06 Mar 2019 08:03:01 GMT

Hi Pradosh,

Thanks for your quick reply but the problem here is I need to do this in a large table with lot of fields.

The condition is If the month is 3 then I need to add 1,2 and 3 etc

Re: Cumulative 12 months Sum in Load Script

pradosh_thakur — Wed, 06 Mar 2019 08:05:21 GMT

did you try what i suggested . The expression will sum rows cumulatively. If you have more rows you may have to tweak it a bit to get the right expression.

-Pradosh

Re: Cumulative 12 months Sum in Load Script

techvarun — Wed, 06 Mar 2019 08:14:12 GMT

I need to achieve this load script

Re: Cumulative 12 months Sum in Load Script

pradosh_thakur — Wed, 06 Mar 2019 08:25:29 GMT

load *,rangesum(peek([Expected Result]),[Amount]) as [Expected Result] inline [
Date ,	Amount,Period	
43374,	10,	1
43405,	20,	2
43435,	30,	3
43466,	40,	4
43497,	50,	5
43525,	60,	6
43556,	70,	7
43586,	80,	8
43617,	90,	9
43647,	100,	10
43678,	110	,11
43709,	120	,12
];

Re: Cumulative 12 months Sum in Load Script

techvarun — Wed, 06 Mar 2019 09:11:13 GMT

Thanks Pradosh,

My source data looks something like the attached i need to accumulate based on Key and %Calkey.

Re: Cumulative 12 months Sum in Load Script

techvarun — Wed, 06 Mar 2019 09:47:13 GMT

Hi Albert,

I will have to calculate the number is load script for lot of reasons.

I have attached the sample data below. Please check if we can calculate this outta load script.

Thanks

Varun

Re: Cumulative 12 months Sum in Load Script

daanciorea — Wed, 06 Mar 2019 12:16:24 GMT

Hi,

You can use this script,

Load Date, Amount, Period, Amount + Previous(Amount) as [Expected Result] From ...