Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try this
=if(Month<=Month(Today()), Rangesum(Above(Sum(Sales),0,RowNo())),Rangesum(Above(Sum(Sales),0,sum(Sales))))
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
Unfortunately that solution does not work. Thank you for your time.
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.
You have to update your your sales expression field name with your sales field name in the data model.
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.
@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])))
I still seem to get an error in expression message using that code.
@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))))