Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
So I cannot use the rangeSum ? I'm accumulating the first column Open AMT
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.
I didn't understand the ASOFDAte that you suggested
BTW several invoices may have the same due date
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.
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')
;