8 Replies Latest reply: Dec 7, 2011 11:05 AM by bgameiro RSS

    Expression Set Analysis

      I want put this expression in qlikview with Set Analysis, anyone know how can i do this?
      This expression takes long time to run .. I need Help!
      =if(count(distinct WT_WorkDateStart=(date(vWsCalendarMon )&' '&text(left(time(TimeFinal),2)) & ':' & date(TimeFinal_1,'mm') & ':' & date(TimeFinal_1,'ss')))=2,1, if(count(distinct WT_WorkDateEnd=(date(vWsCalendarMon )&' '&text(left(time(TimeFinal),2)) & ':' & date(TimeFinal_1,'mm') & ':' & date(TimeFinal_1,'ss')))=2,2,0))
        • Expression Set Analysis
          Miguel Angel Baeyens de Arce

          Hi,

           

          It seems you are getting a timestamp (day, month, year, hours, minutes and seconds) and you are doing a lot of string working with that. QlikView has some date and timestamp functions that can help you doing that part. Can you please share an example of a record for WT_WorkDateStart, TimeFinal and TimeFinal_1 so we can see its format?

           

          On the other hand, the possible values of that expression, if I'm not wrong are 0, 1 and 2. Set analysis will not return that, and you will need a conditional If() anyway, but it may help you with the string functions. In call cases, passing that calculation to the script may be a good idea and worth trying, since it will improve your chart performance notably.

           

          Regards.

           

          Miguel

            • Expression Set Analysis

              Hi Miguel, thanqs for your answer, but i thought in Set Analysis it was very faster, am I wrong?

               

              Regards

                • Expression Set Analysis
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  You're right: set analysis is faster than any conditional. Actually the larger your data set is, the faster set analysis will perform compared to an If(), to the extent that in some cases an If() will cause an error, and set analysis will eventually render the chart.

                   

                  But set analysis cannot replace a conditional in some cases, and you may need an If() for that chart. As I mentioned above, set analysis is evaluated once and for the whole chart, and then the expressions are evaluated based on that set of selections (set analysis is no more and no less than a set of field selections).

                   

                  In other words, set analysis cannot return a conditional value (1 here, 2 there, 3 everywhere...) that seems to be your case. That's why I asked about your data and your requirements, because if your dataset is huge enough, you may need to pass some aggregations to the script so you needn't a conditional.

                   

                  Hope that makes some sense.

                   

                  Miguel