topic Re: RangeSum Starting at Zero in App Development

RangeSum Starting at Zero

Wed, 22 Dec 2021 20:40:09 GMT

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

sunny_talwar — Thu, 25 May 2017 10:41:56 GMT

May be this

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

Re: RangeSum Starting at Zero

Thu, 25 May 2017 10:46:27 GMT

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

sunny_talwar — Thu, 25 May 2017 10:48:27 GMT

Not sure I understand.....

The above doesn't activate them both

both? activate? can you elaborate?

Re: RangeSum Starting at Zero

Thu, 25 May 2017 10:53:04 GMT

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

sunny_talwar — Thu, 25 May 2017 10:55:30 GMT

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

Thu, 25 May 2017 11:19:42 GMT

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

Thu, 25 May 2017 11:31:52 GMT

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

sunny_talwar — Thu, 25 May 2017 11:39:53 GMT

That's awesome