3 Replies Latest reply: Nov 15, 2017 11:36 AM by Marcus Sommer RSS

    Complex expression doesn't allow to open qvw with many data

    ADRIANO STEANELLI

      Dear QV experts,

       

      I've a *.qvw document with a line-chart, where we've 3 dimension fields and 1 complex expression, like:

       

      Dimensions

      • contentid (Enable Conditional =0)
      • consumption (Enable Conditional =0)
      • =Aggr(num(Rank(Sum({<contentid=>} consumption),4)), contentid)

       

      Expression

      • =2*((Rangesum(below(TOTAL (Aggr(num(Rank(Sum({<contentId=>} consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))),0,max(TOTAL(Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid))))))*(Rangesum(below(TOTAL (1/(Rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid))))))),0,max(TOTAL(Aggr(num(Rank(Sum({<contentid=>} consumption),4)),contentid)))))))-1

       

      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.

       

      Additional info:

      • 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
      • Attached, a sample data

       

      Thanks in advance.

        • Re: Complex expression doesn't allow to open qvw with many data
          Marcus Sommer

          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.

           

          - Marcus

            • Re: Complex expression doesn't allow to open qvw with many data
              ADRIANO STEANELLI

              Hi Marcus,

               

              thanks for your feedback, I've done as you said and below the best way of my review:

               

              =2*((Rangesum(below((Aggr(num(Rank(Sum(consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))),0,max(TOTAL(Aggr(num(Rank(Sum(consumption),4)),contentid))))))*(Rangesum(below((1/(Rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (Aggr(num(Rank(Sum(consumption),4)),contentid))))))),0,max(TOTAL(Aggr(num(Rank(Sum(consumption),4)),contentid)))))))-1

               

              but nothing is changed. I've also splitted the entire expression into some variables declared on Variables Overview like this:

               

              MaxRank =max(TOTAL(aggr(num(rank(sum(consumption),4)),contentid)))

              k1 =aggr(num(rank(sum(consumption),4)),contentid)*(sum(consumption)/sum(TOTAL consumption))

              k2 =1/(rangesum(below(TOTAL sum(consumption/sum(TOTAL consumption)),0,max(TOTAL (aggr(num(rank(sum(consumption),4)),contentid))))))

              ECS =2*((rangesum(below($(k1),0,$(MaxRank))))*(rangesum(below($(k2),0,$(MaxRank)))))-1

               

              but nothing is changed.

               

              If you are interested, I've attached an excel file with calculation and for your reference you can find formula at the following link Appendix section..

               

              Many thanks