The expression calculates the effective catalog size (ECS). It is a metric that describes how spread viewing is across the items in our catalog. If most viewing comes from a single video, it will be close to 1. If all videos generate the same amount of viewing, it is close to the number of videos in the catalog. Otherwise it is somewhere in between.
In test environment, *.qvw document working fine, refresh takes 30sec, app size is 300KB and contains 7.000 rows. Into production environment when open the document, appear an error popup with message: "there was a problem sending the command to the program". Here, refresh takes 2min, app size is 20MB and contains 7.000.000 rows.
Probably expression is much complex to be calculate on the fly, but unfortunately can't be calculated a backend, because data depends by selection on Calendar fields. As default, expression is calculated for a single day.
i'm using QlikView 12.10 SR7
I can't share my *.qvw document
HW specs, for Test 4 processors * 2Ghz and 64GB RAM, for Prod 16 processors * 2Ghz and 128GB RAM
I'm not surprised that this is quite slow to uncalculable by so many aggr and TOTAL within the interrecord-function below. I have the feeling that not all of the aggr, below and TOTAL are necessary and it could be simplified in some way.
I suggest that you make a re-start with this task beginning with rather simple sum/count expressions and if they return the expected results and needs to consolidated on step more if there are simpler solutions as just using aggr - especially by including some script pre-calculations.
To separate expressions or parts of it in variables is definitely useful to improve the readability and maintainability but it won't increase the performance (unless it leads to some precalculation and not an expression is returned else a value) because the executed expression itself didn't change.
I meant to check if you really need all the aggr-functions, see: