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: 
vjoshi2017
Contributor
Contributor

Cumulative sum for months when no transaction in month

Hi,

I am trying to show cumulative sum over the months, but there are no transactions in some month. i still want to show total upto previous month though. in next month, if there is a transaction, the sum should be updated.

For example, right now my chart looks like this :

cum.PNG

But I want to show 116 for the month of April and May as well. Then 167 for Month of June which is correct. Is this possible?

The formula I am using for cumulative sum is :

Aggr(RangeSum(Above(Sum(Qty_Ord), 0, RowNo())),Year,Month)

Thanks,

Vivek

5 Replies
mtucholski
Creator
Creator

Hello,

you need to create a master calendar (there is several ways to do it - you wll find it easily).

The master calendar will fill every gap in your dates.

And then Accumulate expression in expressions tab.

Check the file sample.

sunny_talwar

You can either use The As-Of Table or try this

Aggr(RangeSum(Above(Sum(Qty_Ord) + Sum({1} 0), 0, RowNo())), Year, Month)

vjoshi2017
Contributor
Contributor
Author

This is not working. I am using this formula . I am getting the same result.

sunny_talwar

Then I would suggest you to look into the AsOfTable

lfholland
Creator
Creator

This is what I do for cumulative:

sum(enter your formula here)
+
If(Above(TOTAL [name of this field]) > 0, Above(TOTAL [name of this field]), 0)