Help with Set Analysis tunning

Tue, 22 Nov 2011 16:52:47 GMT

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)

                ,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

topic Help with Set Analysis tunning in QlikView

Help with Set Analysis tunning