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
