topic Re: RangeSum performance issue in QlikView

RangeSum performance issue

johngouws — Tue, 26 May 2015 13:24:04 GMT

Afternoon,

I am having a serious performance issue when using RangeSum in a expression. Even when restricting my data set to just over 40000 rows, the chart takes a really long time to resolve. About 3 minutes!! When the model goes into production it will be loading significantly more!! I have already done things like having to select a single 'Client' before the chart displays and calculates, but it actually makes no difference.

The attached example is a simplified example of what I am doing. The example includes a simple expression and has the 'real world' expression commented out.

The "real world" Amount expressions is: =sum({<Acc_GN_ChartID={'60008'} >} Amount).

The "real world" RangeSum expression is: =Rangesum(above(total Sum({<Acc_GN_ChartID={'60008'} >} Amount),0,rowno(total))).

I would appreciate advice on the expression or pointers when I am going wrong.

Thank you,

John

Re: RangeSum performance issue

fvelascog72 — Tue, 26 May 2015 13:33:54 GMT

Hi,

Have you think about calculate in script:

FACT:

LOAD

Date,

Description,

Amount,

if(RowNo()=1,Amount,Amount+Peek(TEST)) as "TEST";

LOAD * INLINE [

Date, Description, Amount,

2014-11-26, Line 1, 70028.79

2014-11-26, Line 2, 341.28

2014-11-26, Line 3, 349.98

2014-11-26, Line 4, 64507.00

2014-11-26, Line 5, 3743.00

2014-12-05, Line 6, -2316.17

2015-01-14, Line 7, -2316.17

2015-02-14, Line 8, -2316.17

2015-03-14, Line 9, -2316.17

2015-04-14, Line 10, -2316.17

];

exit script;

Re: RangeSum performance issue

swuehl — Tue, 26 May 2015 13:36:07 GMT

John,

when you are removing the set analysis from your expressions, it runs in seconds, just by putting in the set expression slows it down to minutes? Is only the rangesum expression affected or also the pure sum expression?

Your sample doesn't show the data model you are using (at least the field Acc_GN_ChartID is not part of your sample, and I assume it's not just part of the fact table). Could you post some more details about your data model?

Or even better, a sample that demonstrates your issue (i.e. there is a significant impact on performance when using the expression with set analysis put in).

Re: RangeSum performance issue

johngouws — Tue, 26 May 2015 13:41:41 GMT

Hi Swuehl,

You are correct, the example is over simplified. I will see how I can get the pre-prod model posted.

Thank you for your reply,

John

Re: RangeSum performance issue

johngouws — Tue, 26 May 2015 13:44:43 GMT

Hi Federico,

It is something worth trying, I am assuming that I will have to get my source data to be sorted Date, Description? That may be easier said that done.

I will get back to you with a result,

John

Re: RangeSum performance issue

Clever_Anjos — Tue, 26 May 2015 13:45:20 GMT

What you are doing is creating an accumulation column, right?

The native way (Accumulation/Full Accumulation) does not fit your needs?

Re: RangeSum performance issue

johngouws — Tue, 26 May 2015 13:54:23 GMT

That is what I am trying to do,

If you select 'Full Accumulation" on the Amount Expression, it makes no difference.

Re: RangeSum performance issue

Clever_Anjos — Tue, 26 May 2015 14:13:00 GMT

Would you mind sharing a sample of your app? It should work as designed