Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MAPSF1
Contributor III
Contributor III

Cumulative sum in script

Hi All, 

I'm having some difficulty with summing some rows, in order to create a running 12 month total field. Because of the nature of the data, I have to create the result as a dimension. I want to show the total 12 months combined, for any given month. 

The data is shown like so, with my own added Expected Total column. 

PERIOD YEAR MONTH CONTRACT SALES   EXPECTED TOTAL
201210 2012 10 123 1   1
201211 2012 11 789 3   3
201212 2012 12 123 4   5
201301 2013 1 123 4   9
201302 2013 2 456 8   8
201303 2013 3 123 3   12
201304 2013 4 123 2   14
201305 2013 5 123 6   20
201306 2013 6 456 8   16
201307 2013 7 789 4   7
201308 2013 8 456 2   18
201309 2013 9 333 9   333
201310 2013 10 456 7   25
201311 2013 11 456 10   35
201312 2013 12 123 3   22
201401 2014 1 789 5   9
201402 2014 2 123 7   21
201403 2014 3 789 4   13
201404 2014 4 789 3   16
201405 2014 5 456 8   35
201406 2014 6 789 7   23
201407 2014 7 789 9   28
201408 2014 8 789 4   32
201409 2014 9 123 3   13
201410 2014 10 456 2   20
201411 2014 11 123 1   14
201412 2014 12 789 1   21

 

I have tried a few different methods, such as Peek & Above functions, with the main problem being when the year ends, and only the months within that year are included in the total, with the previous year not included. So I'm not sure if it's a sorting issue or not? 

Anybody able to point me in the correct direction?

Thanks in advance. 

 

14 Replies
MAPSF1
Contributor III
Contributor III
Author

there are multiple CONTRACTs per PERIOD. Maybe that is where the issue is? 

When I use Rohan's code, I am getting a cumulative total for the entire data set, as opposed to just the specific total per contract per period. 

MAPSF1
Contributor III
Contributor III
Author

Yeah, I think what Rohan's code is doing is taking the previous 12 month's SALES, and summing that. It's not taking into consideration that it should only be for the particular CONTRACT, within that 12 month rolling period. 

chrismarlow
Specialist II
Specialist II

Hi,

Change the end of the code to something like;

Temp :
load
date(addmonths($(min),'$(i)')) as PDate,
Contract,
sum(Sales) as ccsales
Resident Data where date(PDate)<=date(addmonths('$(min)',$(i))) and
date(PDate)>=date(addmonths(addmonths('$(min)',$(i)),-11))
GROUP BY date(addmonths($(min),'$(i)')), Contract;

Next i;


LEFT JOIN (Data)
Load * resident Temp;

drop table Temp;

Cheers,

Chris.

MAPSF1
Contributor III
Contributor III
Author

 This is exactly it. Thank you very much for your help, both Chris & Rohan. 

Rohan
Specialist
Specialist

Hi

I was about to write the same thing 😁, Only thing I would suggest is don't join the table in the end because then there would be multiple rows for ccSales  & the sum(ccSales) may  go wrong. Do check once before the join part.