Announcements
cancel
Showing results for
Did you mean:
Not applicable

## 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

Labels (1)
• ### function

1 Solution

Accepted Solutions
MVP

May be this

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

8 Replies
MVP

May be this

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

Not applicable
Author

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

MVP

Not sure I understand.....

 The above doesn't activate them both

both? activate? can you elaborate?

Not applicable
Author

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

MVP

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

Not applicable
Author

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

Not applicable
Author

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!

MVP

That's awesome