8 Replies Latest reply: May 25, 2017 7:39 AM by Sunny Talwar

# RangeSum Starting at Zero

Hi All

Many thanks for the support on this one,

I'm looking for a way to start a RangeSum Accumilation at Zero when the data range isn't zero at the original start point.

For the attached Excel I can use RangeSum(Above(Return(Value),0,rowno())) to see the accumilation of totals which is great but If I needed that initial point to start at Zero (Without just writing 0 on the Excel sheet) is there a way to have the Rangesum start at Zero before starting the Ranged Sum please?

MAny thanks

• ###### Re: RangeSum Starting at Zero

May be this

If(RowNo() = 1, 0, RangeSum(Above(Return(Value), 0, RowNo())))

• ###### Re: RangeSum Starting at Zero

Hi Sunny, cheers for the rapid reply,

The above doesn't activate them both, so it will always have a RowNo 1 so will set it to 0 and won't then do the RangeSum giving you a horizontal 0 line on the chart.

Many thanks

Ryan

• ###### Re: RangeSum Starting at Zero

Not sure I understand.....

The above doesn't activate them both

both? activate? can you elaborate?

• ###### Re: RangeSum Starting at Zero

Sorry, not sure the best way to explain but if you use the attached Excel and use that Expression in a line chart you'll see what I mean, it makes every RowNo = 0

• ###### Re: RangeSum Starting at Zero

That's not true... When I use RowNo() as the expression, I see a upward sloping line like this

UPDATE: So this tells me that RowNo() is not 0 and keeps on changing based on dimension

• ###### Re: RangeSum Starting at Zero

Hi Sunny,

Apologies your absolutely right, reason it wasnt working for me was because I have multiple Products in the main App which means the first Product is RowNo 0 and then everyone else isnt (Due to an AGGR sorting on Product name and then Date)

Potentially then a mix of Min(Date) to try and show it Zero on the mindate or something else,

Appreciate the help

• ###### Re: RangeSum Starting at Zero

Hi Sunny,

Got it working

Your IF statement was the key so thank you, for the actual date set it went a bit more complex and I used a flag to set start dates but otherwise spot on

If(Date = min({\$<[#RecommendationFlag] = {1}>} total Date), 0, Max(Aggr(rangesum(above(sum({\$<[#RecommendationFlag] = {1}>} return),0,rowno())),[Product Name], Date)))

Thanks again!

• ###### Re: RangeSum Starting at Zero

That's awesome