topic Re: doubt regarding cumulative sum in qlikview in QlikView

doubt regarding cumulative sum in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have doubt regarding how to write an expression for cumulative sum in a row, my requirement is as follows:

jan feb mar apr may jun

sale1 10 20 30 40 50 60

sale2 20 30 40 60 80 100

m1 10 10 10 20 30 40

m2 10 20 30 50 80 120

here m1= sale 2- sale1

m2 is previous month m2 plus current month m1

i.e., m2 of feb = 10(previous month m2) +10(current month m1)=20

m2 of mar= 20+ 10=30

Kindly help how to write this expression required as per my requirement

its_anandrjs — Tue, 29 Nov 2016 15:38:18 GMT

Load your table this ways

Data:

LOAD *,sale2-sale1 as m1;

LOAD * Inline

[

Month,sale1,sale2

jan,10,20

feb,20,30

mar,30,40

apr,40,60

may,50,80

jun,60,100

];

NoConcatenate

LOAD *,

RangeSum(m1, Peek('m2')) AS m2

Resident Data;

DROP Table Data;

Regards,

Anand

its_anandrjs — Tue, 29 Nov 2016 15:45:00 GMT

After loading create the pivot table

Dim1:- Month

Expr1:- =sum(sale1)

Expr2:- =sum(sale2)

Expr3:- =sum(m1)

Expr4:- =sum(m2)