0 Replies Latest reply: Nov 22, 2011 11:52 AM by esepulveda RSS

    Help with Set Analysis tunning

      Hi everyone, I was trying to get an expression a little bit more performant,since is taking long time to load on a document, here is the expression:

       

      if(
          
      count(if(ContactStateID=4,ContactStateID))>0
          
      and
          
      count(if(IsInQueue=1,IsInQueue))>0
           ,
          
      sum(
                
      aggr(
                     
      //Expression               

                          sum(
                           {
                           $<
                          
      ContactID =

                                    P({$<ContactStateID={4}>} ContactID)

                                    *

                                    P({$<IsInQueue={1}>} ContactID)

                                    *

                                    E({$<ContactStateID={1}>} ContactID),
                          
      IsInQueue={1},
                          
      ContactDuration={">0"},
                          
      ContactStartDate={">=$(varCalendarStartDateTZ)<$(varCalendarEndDateTZ)"}
                           >}
      ContactDurationQH
                           /
                          
      varTimeMeasure      
                      )              
                     
                     
      //Dimensions               

                      ,ContactID
                      ,
      ContactStartDateQH      
                 )
           )
      )

       

      Basically the logic goes like this:

      In the beginning the metric was just the following:

       


          
      sum(
                
      aggr(
                     
      //Expression               

                          sum(
                           {
                           $<
                          
      ContactID =

                                    P({$<ContactStateID={4}>} ContactID)

                                    *

                                    P({$<IsInQueue={1}>} ContactID)

                                    *

                                    E({$<ContactStateID={1}>} ContactID),
                          
      IsInQueue={1},
                          
      ContactDuration={">0"},
                          
      ContactStartDate={">=$(varCalendarStartDateTZ)<$(varCalendarEndDateTZ)"}
                           >}
      ContactDurationQH
                           /
                          
      varTimeMeasure      
                      )              
                     
                     
      //Dimensions              

                      ,ContactID
                      ,
      ContactStartDateQH      
                 )

       

      But the initial if:

       

      if(
          
      count(if(ContactStateID=4,ContactStateID))>0
          
      and
          
      count(if(IsInQueue=1,IsInQueue))>0

       

      Was added in order to test some conditions before actually performing the costly AGGR function.

       

      I was trying to come up with a simpler version of the following script:

       

      ContactID =

           P({$<ContactStateID={4}>} ContactID)

           *

           P({$<IsInQueue={1}>} ContactID)

           *

           E({$<ContactStateID={1}>} ContactID)


      But I am having a hard time trying to replicate this inclusive/exclusive (p() and e()) logic, so far I had tried the following:

       

      if(
           Count({$<ContactID = P({$<ContactStateID={4}>} ContactID)>}ContactID) > 0

           and

           Count({$<ContactID = P({$<IsInQueue={1}>} ContactID)>}ContactID) > 0

           and

           Count({$<ContactID = P({$<ContactStateID={1}>} ContactDurationQH)>}ContactID) = 0

                     
                 ,

       

       

      Or even trying to get rid of the whole inner Aggr expresison and substituting it for this:

       

      Sum(

           {$<
                ContactDurationQH   =    P({$<ContactStateID={4}>} ContactDurationQH)
                ,
      ContactDurationQH  =    P({$<IsInQueue={1}>} ContactDurationQH)
                ,
      ContactDurationQH  =    E({$<ContactStateID={1}>} ContactDurationQH)
           >}

           ContactDurationQH)

       

      But is not working.

       

      By any chance, is there any suggestion to improve the initial expression? Unfortunately, they do not allow me to touch the load script, so pre calculated metrics wont be an option.

       

      Thanks in advance for your time.

       

      -ed