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

# 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.