Help in speeding up response

[ali_hijazi]

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

[johnw]

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.

[ali_hijazi]

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

[johnw]

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.

[ali_hijazi]

I didn't understand the ASOFDAte that you suggested

BTW several invoices may have the same due date

[johnw]

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')
;