Accumulation Month Over Month

[djt9006]

How can I suppress the accumulation sum of a column for months that have not yet come.

For instance if the current month is April, how would I make sure that the accumulative total for the month of May, June, July etc. all show a total of $0.

Month	Sales	Accumulation
Jan	10	10
Feb	20	30
Mar	30	60
Apr	40	100
May	0	0
Jun	0	0
Jul	0	0

[sandeep-singh]

Try this

=if(Month<=Month(Today()), Rangesum(Above(Sum(Sales),0,RowNo())),Rangesum(Above(Sum(Sales),0,sum(Sales))))

[stevedark]

Hi @djt9006 

To see an alternative method of accumulating values please see this blog post:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Using this technique you can have a separate dimension which you can then only create up to the current date. It has the advantage over the rangesum(above approach that it accumulates values ahead of what is selected in the chart.

Hope it is of interest.

Steve

[djt9006]

Unfortunately that solution does not work. Thank you for your time.

[djt9006]

Thank you for passing this information along. I am not at the stage where I can understand which part(s) of the solution I should use.

[sandeep-singh]

You have to update your your sales expression field name with your sales field name in the data model. 

[djt9006]

I created a Master Item that calculates the sum of Sales and named it "TEST". I substituted the word "Sales" in the code you provided me with TEST but I still received an error.

[Kushal_Chawda]

@djt9006  two observations

1) Your month field in text format so, month comparison will not work, so try

=if( month(date#(Month,'MMM'))<= month(today()),.....

2) You cannot put aggregation function over master measure, just simply call master measure as [TEST]

So, your expression could be

=if( month(date#(Month,'MMM'))<= month(today()), Rangesum(Above([TEST],0,RowNo())),Rangesum(Above([TEST],0,[TEST])))

[djt9006]

I still seem to get an error in expression message using that code.

[Kushal_Chawda]

@djt9006  the I would suggest replace 'TEST' with actual measure like 'Sum(Value)'

=if( month(date#(Month,'MMM'))<= month(today()), Rangesum(Above(sum(Value),0,RowNo())),Rangesum(Above(sum(Value),0,sum(Value))))