Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Odd behaviour with rangesum(before(sum function in pivot chart.

Having difficulties with this formula, no matter how simple I make it.  Basically I'm dealing with financial data, where a field needs to represent a running sum (aggregated to the field value to the left of it, for the previous fiscal period).

When I isolate just a single row of data, the aggregated sums are represented correctly.

But when I have multiple rows (ie. separate balance sheet items), the formula doesn't consistently add to the previous - before - value, but seems to sometimes reset along the way.

Simple example that I'm using that shows this issue: =rangesum(before(sum(Budget),0,ColumnNo()))

Anyone run into a similar issue?  I must be doing something wrong here.

Thanks,

Matt

7 Replies
MVP & Luminary
MVP & Luminary

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

Try =rangesum(before(sum(Budget),0,ColumnNo(TOTAL)))


talk is cheap, supply exceeds demand

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

Do we need another TOTAL?

=RangeSum(Before(TOTAL Sum(Budget), 0, ColumnNo(TOTAL)))

MVP & Luminary
MVP & Luminary

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

Probably, good call.


talk is cheap, supply exceeds demand
Not applicable

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

I've tried both of your suggestions and the values shown are consistent with what they were before (incorrect when multiple rows are showing).

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

Would you be able to share a sample or some screenshots? We might be missing something here

Not applicable

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

correct (isolated selection).png

This is correct (isolated a single IncomeGroup). 

incorrect.png

This is the same incomegroup shown without being filtered out, showing the same periods.  Note I have the same issue for both Income and Budget expressions.

Re: Odd behaviour with rangesum(before(sum function in pivot chart.

Are you using if statements or set analysis in your expression to restrict something? I am not 100% sure what might be going on here....