topic Cumulative sum by month in load in QlikView

Cumulative sum by month in load

Antoine5531 — Thu, 29 Jul 2021 07:42:35 GMT

Hello,

I'm trying to add a variable to my data that would sum the values of all the previous month of this year + the actual month.

Here is an example :

Year	Month	id	Amount	New variable
2021	01	a	10	10
2021	02	b	10	20
2021	03	c	10	50
2021	03	d	10	50
2021	03	e	10	50
2021	04	f	10	60
2021	05	g	10	80
2021	05	h	10	80
2021	06	i	10	90

I manage to do it in a table but i'm struggling to do it in the script pls help.

Thanks.

Re: Cumulative sum by month in load

MayilVahanan — Thu, 29 Jul 2021 08:56:27 GMT

Try like below

Test1:
LOAD * INLINE [
Year, Month, id, Amount
2021, 01, a, 10
2021, 02, b, 10
2021, 03, c, 10
2021, 03, d, 10
2021, 03, e, 10
2021, 04, f, 10
2021, 05, g, 10
2021, 05, h, 10
2021, 06, i, 10
];

Join
Load *, SumAmount+Alt(Peek('NewVariable'),0) as NewVariable;
Load Year,Month, Sum(Amount) as SumAmount Resident Test1 group by Year, Month;

Re: Cumulative sum by month in load

aditya12345 — Thu, 29 Jul 2021 09:03:18 GMT

You can use the below -

LOAD Year,
Month,
id,
Amount,
if(Year = previous(Year), Rangesum(Amount,peek([New variable])), Amount) as [New variable]
FROM
[<PATH>]
(ooxml, embedded labels, table is Sheet1);

Re: Cumulative sum by month in load

JuMo — Tue, 25 Mar 2025 12:00:52 GMT

Is it possible to do the same thing but calculating the sum amount by product type ??

Test1:
LOAD * INLINE [
Year, Month, p_type, Amount
2021, 01, a, 10
2021, 01, b, 10
2021, 02, a, 10
2021, 02, a, 10
2021, 02, b, 10
2021, 03, a, 10
2021, 03, a, 10
2021, 03, b, 10
2021, 03, b, 10
];

Re: Cumulative sum by month in load

BrunPierre — Wed, 26 Mar 2025 07:58:38 GMT

What's your expected output? @JuMo