Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
CuriousGeorge
Contributor II
Contributor II

Cumulative sum group by year in a pivot table

I am trying to get a cumulative sum group by year, and every year it should start and use the value for Jan. 

Is there a way to do this? I have tried all sorts of method but it doesn't work. 

My current expression works for 2021, but it messed up 2022 & doesn't show the correct value for Jan-2022. 

 

RangeSum(Above(TOTAL Sum(Amount), 0, RowNo(TOTAL)))

 

 

 

CuriousGeorge_0-1641192035936.png

 

Labels (4)
1 Solution

Accepted Solutions
CuriousGeorge
Contributor II
Contributor II
Author

Ok I solve my own problem. I had another column field named Quarter with values e.g. Q1, Q2, etc. Apparently this messed up the calculation and didn't allow to group by year. I removed quarter col field and "total" in the set expression. 

 

RangeSum(Above(Sum(Amount), 0, RowNo()))

 

Again, I will be happy if someone can let me know what to do if need quarter in the col field. 

 

View solution in original post

2 Replies
tresesco
MVP
MVP

If you have only these (year and month) dimensions, you could try removing 'total' qualifier, like:

RangeSum(Above( Sum(Amount), 0, RowNo()))
CuriousGeorge
Contributor II
Contributor II
Author

Ok I solve my own problem. I had another column field named Quarter with values e.g. Q1, Q2, etc. Apparently this messed up the calculation and didn't allow to group by year. I removed quarter col field and "total" in the set expression. 

 

RangeSum(Above(Sum(Amount), 0, RowNo()))

 

Again, I will be happy if someone can let me know what to do if need quarter in the col field.