Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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. |
Could you post your expression?
edited:
Are you using Above() or similar inside the Rangesum?
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.
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
This might be another approach
Sunny T <span class="icon-status-icon icon-mvp" title="Mvp"></span> wrote:
This might be another approach
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):
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
;
Thank you Stafan,Sunny and gysbert. I try to use another approach.