Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vjoshi2017
New 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

Tags (2)
5 Replies
mtucholski
Contributor

Re: Cumulative sum for months when no transaction in month

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.

Re: Cumulative sum for months when no transaction in month

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
New Contributor

Re: Cumulative sum for months when no transaction in month

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

Re: Cumulative sum for months when no transaction in month

Then I would suggest you to look into the AsOfTable

lfholland
New Contributor III

Re: Cumulative sum for months when no transaction in month

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)

Community Browser