topic Re: Help in speeding up response in QlikView

Help in speeding up response

ali_hijazi — Fri, 30 Dec 2016 15:35:07 GMT

Hello

Got a pivot table in which I display customer, InvoiceNumber, Invoice Date, and InvoiceDue date

as expression I'm calculating the amount remaining

and another column showing the cumulative amount which resets by at ever other customer

the pivot table is sorted by Due Date

Now for this selected salesman the report is taking around 2.5 minutes to respond and I'm sure it is because of the cumulative amount expression in which I'm using range sum with aggr and sorting, if I disable this expression the reports responds in less than a second

Please advise how I can speed up the response of this expression

Check attached QVW

Re: Help in speeding up response

johnw — Fri, 30 Dec 2016 17:37:15 GMT

I've not looked at all the variables being used and such, or tried to figure out exactly what's being accumulated and from what rows. But I'm guessing you're accumulating all previous due dates within the Is Active, Routing, Customer, and Type dimensions? I think this is a good case for an AsOf table linking every date to every previous date. Basically, generate a table that looks like this:

AsOf Due Date,Days Back,Due Date
19-03-2017,0,19-03-2017
19-03-2017,1,18-03-2017
19-03-2017,2,17-03-2017
...
19-03-2017,365,19-03-2016
...
18-03-2017,0,18-03-2017
18-03-2017,1,17-03-2017
...

Use AsOf Due Date as the dimension instead of Due Date. Modify expressions reporting for a single due date to add set analysis expression {<[Days Back]={0}>}. The cumulative expression needs no such set analysis, as the data model itself handles the accumulation. That should make it dramatically faster to calculate.

Re: Help in speeding up response

ali_hijazi — Fri, 30 Dec 2016 18:23:04 GMT

So I cannot use the rangeSum ? I'm accumulating the first column Open AMT

Re: Help in speeding up response

johnw — Fri, 30 Dec 2016 18:29:18 GMT

You CAN use rangesum(), but I think that's a big part of what's slowing your chart down. So I would suggest using a different approach.

Re: Help in speeding up response

ali_hijazi — Fri, 30 Dec 2016 19:21:54 GMT

I didn't understand the ASOFDAte that you suggested

BTW several invoices may have the same due date

Re: Help in speeding up response

johnw — Fri, 30 Dec 2016 19:51:49 GMT

Here's some general information about this sort of table, this technique. I'm confident I wouldn't be able to explain it better than Henric does.

The As-Of Table

There's no problem with multiple invoices being on the same date. He's using months in his example instead of dates, but it's exactly the same idea. And he's showing multiple transactions in a month, the equivalent in your data of multiple invoices for the same date.

Well, here you go. I made an example with Invoices and an AsOfDate. Building the AsOf calendar is simple. Just substitute your Date field in this part of the script. It doesn't need to know your table.

AsOf:
LOAD *
,AsOfDate - Date as DaysBack
WHERE Date <= AsOfDate
;
LOAD
date(fieldvalue('Date',recno())) as AsOfDate
,date(fieldvalue('Date',iterno())) as Date
AUTOGENERATE fieldvaluecount('Date')
WHILE iterno() <= fieldvaluecount('Date')
;