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

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

Tue, 05 Jul 2016 14:59:37 GMT

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

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

Gysbert_Wassenaar — Tue, 05 Jul 2016 15:20:52 GMT

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

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

sunny_talwar — Tue, 05 Jul 2016 15:28:27 GMT

Do we need another TOTAL?

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

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

Gysbert_Wassenaar — Tue, 05 Jul 2016 15:30:13 GMT

Probably, good call.

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

Tue, 05 Jul 2016 15:51:27 GMT

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.

sunny_talwar — Tue, 05 Jul 2016 15:54:56 GMT

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

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

Tue, 05 Jul 2016 16:26:25 GMT

This is correct (isolated a single IncomeGroup).

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.

sunny_talwar — Tue, 05 Jul 2016 16:50:25 GMT

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