Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
techvarun
Valued Contributor II

Cumulative 12 months Sum in Load Script

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 PeriodExpected Result
4337410110
4340520230
4343530360
43466404100
43497505150
43525606210
43556707280
43586808360
43617909450
4364710010550
4367811011660
4370912012780

 

Many Thanks in advance

9 Replies
pradosh_thakur
Honored Contributor II

Re: Cumulative 12 months Sum in Load Script

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

Learning never stops.
albert_guito
Contributor II

Re: Cumulative 12 months Sum in Load Script

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
Ag+
techvarun
Valued Contributor II

Re: Cumulative 12 months Sum in Load Script

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
pradosh_thakur
Honored Contributor II

Re: Cumulative 12 months Sum in Load Script

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

Learning never stops.
techvarun
Valued Contributor II

Re: Cumulative 12 months Sum in Load Script

I need to achieve this load script
pradosh_thakur
Honored Contributor II

Re: Cumulative 12 months Sum in Load Script

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
];
Learning never stops.
techvarun
Valued Contributor II

Re: Cumulative 12 months Sum in Load Script

Thanks Pradosh,

 

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

 

 

techvarun
Valued Contributor II

Re: Cumulative 12 months Sum in Load Script

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

Partner
Partner

Re: Cumulative 12 months Sum in Load Script

Hi,

You can use this script,

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