topic Re: Cumulative Sum in Qlikview Script in QlikView

Cumulative Sum in Qlikview Script

mmilani12 — Tue, 31 Mar 2020 10:01:25 GMT

Hi everyone,

in the script I need to find a formula to obtain from this table:

date art sales

01/03/2020 011 20

03/03/2020 011 10

04/03/2020 011 5

05/03/2020 011 10

06/03/2020 011 30

the column cumulative as below:

date art sales cumulative

01/03/2020 011 20 20

03/03/2020 011 10 30

04/03/2020 011 5 35

05/03/2020 011 10 45

06/03/2020 011 30 75

I can't find a solution! Please consider this is just a example, the original table has thousands of rows.

Re: Cumulative Sum in Qlikview Script

sunny_talwar — Tue, 31 Mar 2020 11:48:23 GMT

Try a script like this

Table: LOAD * INLINE [ Date, Art, Sales 01/03/2020, 011, 20 03/03/2020, 011, 10 04/03/2020, 011, 5 05/03/2020, 011, 10 06/03/2020, 011, 30 01/03/2020, 022, 40 03/03/2020, 022, 5 04/03/2020, 022, 10 05/03/2020, 022, 5 06/03/2020, 022, 60 ]; FinalTable: LOAD Date, Art, Sales, If(Art = Previous(Art), RangeSum(Peek('Cumulative Sales'), Sales), Sales) as [Cumulative Sales] Resident Table Order By Art, Date; DROP Table Table;

Re: Cumulative Sum in Qlikview Script

m_woolf — Tue, 31 Mar 2020 11:48:34 GMT

Something like:

Load
Date,
Art,
Sales,
If(Peek('Art')=Art,Sales,Peek('Cummulative'),Rangesum(Sales,Cummulative)) as Cummulative
from Whatever
order by Date,Art;