Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

RangeSum to not accumulate future values that have zeroes

Hi All,

For the current fiscal quarter, I am trying to exclude Weeks in the future that do not have any Revenue values yet. Here is what I am doing:

rangesum(before(total sum(Revenue_Value),0,NoOfColumns(TOTAL)))

Problem is my table is looking like this   


2-24-2014 1-30-15 AM.jpg

We only have Weeks 1 to 3(W1-W3) so far.Weeks 4 - 13 have zero values and therefore should not accumulate and should be empty.

Not sure, what I should be doing to achieve this. Any ideas/suggestions are welcome.

Thanks for your help!

Archie

1 Reply
marcus_sommer

There are several approaches possible:

Expression:

rangesum(before(total sum(if(Revenue_Value >0, Revenue_Value)),0,NoOfColumns(TOTAL)))

if(Revenue_Value >0,
rangesum(before(total sum(if(Revenue_Value >0, Revenue_Value)),0,NoOfColumns(TOTAL))))

Dimension (with hide Null):

if(Revenue_Value >0, Week)

if(Date <= today() , Week)

One of them will surely work.

- Marcus