topic Re: ''RangeSum'' high computation in QlikView

''RangeSum'' high computation

Wed, 25 Nov 2020 16:16:04 GMT

I would like to us Qlik ''RangeSum'' function.But for huge data (150000 rows) Rangsum function calculating for long time (more than five minutes) then I must have to stop Qlik and start again.

Re: ''RangeSum'' high computation

swuehl — Mon, 09 Jan 2017 16:27:03 GMT

Could you post your expression?

edited:

Are you using Above() or similar inside the Rangesum?

Re: ''RangeSum'' high computation

Mon, 09 Jan 2017 16:38:48 GMT

Hi Stefan ,

Thank you for the reply.

RangeSum(Above(Sum(Sales), 0, RowNo()))

It works for small data I test on excel. But that huge data I am getting from MS SQL. Thank you.

Re: ''RangeSum'' high computation

swuehl — Mon, 09 Jan 2017 16:47:31 GMT

If you try to calculate some 'Rolling X', you can also try a script based solution like described in

Calculating rolling n-period totals, averages or other aggregations

Qlik Design Blog

Re: ''RangeSum'' high computation

sunny_talwar — Mon, 09 Jan 2017 17:34:13 GMT

This might be another approach

The As-Of Table

Re: ''RangeSum'' high computation

swuehl — Mon, 09 Jan 2017 18:07:41 GMT

Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:

This might be another approach

The As-Of Table

That's actually what I've tried to link to as my second link 😉

But nevermind, it's part of the first link, too (and the AsOf-Table concept is also an important part of the Gysbert's main doc which also discusses some alternatives):

https://community.qlik.com/docs/DOC-4252#comment-46501

Re: ''RangeSum'' high computation

Gysbert_Wassenaar — Mon, 09 Jan 2017 18:27:40 GMT

If you're trying to calculate a cumulative sum over an entire table then perhaps you can calculate it in the script instead. If your source table is already sorted correctly then you could try using the peek function.

LOAD

...some fields...,

OrderDate,

Amount,

Rangesum(Amount, CumulativeAmount) as CumulativeAmount

FROM

...source_table...

;

If you source table is not sorted already then you need to load it first into a temporary table and then use a resident load with an ORDER BY clause to sort it.

LOAD

...some fields...,

OrderDate,

Amount,

Rangesum(Amount, CumulativeAmount) as CumulativeAmount

RESIDENT

...unsorted_temp_table...

ORDER BY

OrderDate

;

And if you need to the cumulative amounts restarted for each value of a dimension (or for combinations of values from several dimensions) you need to add a check for changes of those values:

LOAD

...some fields...,

Product,

OrderDate,

Amount,

If(Previous(Product)=Product, Rangesum(Amount, CumulativeAmount),Amount) as CumulativeAmount

RESIDENT

...unsorted_temp_table...

ORDER BY

Product, OrderDate

;

Re: ''RangeSum'' high computation

Thu, 12 Jan 2017 16:16:51 GMT

Thank you Stafan,Sunny and gysbert. I try to use another approach.