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: 
ben2r
Contributor III
Contributor III

Cumulative Sum by Year and Product in Script (QlikView)

Hi all, 

I am trying to solve a problem in my load script of doing a cumulative sum but where I have multiple years and 3 products over which I want to sum. 

My objective is to have a cumulative sum specific to a year and a product so I can compare the year by year cumulative figure. 

I have been able to setup a cumulative sum in the script using this code: 

 

 

 

IF(Previous(CLIENT) = CLIENT,RangeSum(peek('RELN_iCNR_CUMULATIVE'), AMOUNT)) AS RELN_iCNR_CUMULATIVE​

 

 

 

This works to calculate cumulative across the whole time period. 

If I want to make this work within dimensions I tried this: 

 

 

 

IF(Previous(CLIENT) = CLIENTAND Previous(FACT_YEAR) = FACT_YEAR AND Previous(PRODUCT) = PRODUCT,RangeSum(peek('RELN_iCNR_CUMULATIVE_YR'), AMOUNT)) AS RELN_iCNR_CUMULATIVE_YR

 

Which will work but gives a blank for the first month in a year.

My end point objective is to count where this cumulative sum > 1000 and compare this year by year. For instance if a client makes 500 revenue in Jan, count = 0, 500 in Feb, count = 1 (500+500) and continues as 1 for the rest of the year

Really appreciate any pointers. I may be over complicating this

Labels (1)
1 Solution

Accepted Solutions
ben2r
Contributor III
Contributor III
Author

In case anyone is here looking for the same, I managed to solve the issue from 2 logic steps. 

1) The reason the cumulative sum was not working was initially to do with the order in which I sort the table to do the cumulative calculation. Once I looked at the raw data table and worked out the sequence it became clear. Quick tip here is to do a cumulative sum excluding the extra dimension (i.e. year in this case) and then sort the raw table by this value. That will show you how it is being sorted in practice. 

2) Once 1) was solved I still had the issue of gaps for the first year in each section. Using the insight from here: https://community.qlik.com/t5/QlikView-App-Dev/Script-cumulative-sum-group-by/td-p/1810410 I was able to see a solution of adding an initial logic to say 'if current year is not the same as previous then return the value, else perform the cumulative sum' Resulting code below: 

IF(FIN_FACT_YEAR <> Previous(FIN_FACT_YEAR),FIN_PROD_MONTHLY_iCNR,
		IF(FIN_CLIENT_NBR = Previous(FIN_CLIENT_NBR) AND FIN_FACT_YEAR=Previous(FIN_FACT_YEAR),RangeSum(peek('RELN_iCNR_CUMULATIVE_LOGIC'), FIN_PROD_MONTHLY_iCNR))) AS RELN_iCNR_CUMULATIVE_LOGIC

View solution in original post

3 Replies
marcus_sommer

There is no else-branch within the if-loop. You may it trying in this way:

load *, -(RELN_iCNR_CUMULATIVE_YR >= 1000) as RevenueFlag;
load *,
IF(Previous(CLIENT) = CLIENT AND
     Previous(FACT_YEAR) = FACT_YEAR AND
     Previous(PRODUCT) = PRODUCT,
RangeSum(peek('RELN_iCNR_CUMULATIVE_YR'), AMOUNT),
AMOUNT) AS RELN_iCNR_CUMULATIVE_YR
resident Source
order by CLIENT, FACT_YEAR, PRODUCT;

 

ben2r
Contributor III
Contributor III
Author

In case anyone is here looking for the same, I managed to solve the issue from 2 logic steps. 

1) The reason the cumulative sum was not working was initially to do with the order in which I sort the table to do the cumulative calculation. Once I looked at the raw data table and worked out the sequence it became clear. Quick tip here is to do a cumulative sum excluding the extra dimension (i.e. year in this case) and then sort the raw table by this value. That will show you how it is being sorted in practice. 

2) Once 1) was solved I still had the issue of gaps for the first year in each section. Using the insight from here: https://community.qlik.com/t5/QlikView-App-Dev/Script-cumulative-sum-group-by/td-p/1810410 I was able to see a solution of adding an initial logic to say 'if current year is not the same as previous then return the value, else perform the cumulative sum' Resulting code below: 

IF(FIN_FACT_YEAR <> Previous(FIN_FACT_YEAR),FIN_PROD_MONTHLY_iCNR,
		IF(FIN_CLIENT_NBR = Previous(FIN_CLIENT_NBR) AND FIN_FACT_YEAR=Previous(FIN_FACT_YEAR),RangeSum(peek('RELN_iCNR_CUMULATIVE_LOGIC'), FIN_PROD_MONTHLY_iCNR))) AS RELN_iCNR_CUMULATIVE_LOGIC
ben2r
Contributor III
Contributor III
Author

Thanks Marcus - I like the flag option here I had not thought of that. I was going to do the calculation in the dashboard for this part but the flag may be cleaner and faster no doubt