Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

1 Solution

Accepted Solutions
MVP
MVP

Re: RangeSum Starting at Zero

May be this

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

8 Replies
MVP
MVP

Re: RangeSum Starting at Zero

May be this

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

Not applicable

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

MVP
MVP

Re: RangeSum Starting at Zero

Not sure I understand.....

The above doesn't activate them both

both? activate? can you elaborate?

Not applicable

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

MVP
MVP

Re: RangeSum Starting at Zero

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

Capture.PNG

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

Not applicable

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

Not applicable

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!

MVP
MVP

Re: RangeSum Starting at Zero

That's awesome