Help with Set analysis - Part II

[#1]

Hi Everyone some days ago i did ask a similar question and got very useful feedback, I hope i can get some help again

The scenario is similar, on a straight table,  I have grouped rows on each row, since its dimention is based on Date, (ex. Quarter Hour, Day, etc)

Now the problems is that in an expression, no mater what is the dimention grouping (by Quarter Hour or Day)  I must calculate a sum based on the following criteria:

1. There is a (ContactSateID = 4) in the current bucket
2. There is a (IsInqueue = 1) in the current bucket

Then is that is met, calculate the sum of (ContacDuration) or the (IsInQueue = 1) between the:

Max(${<ContactStateID  = 4>} ContactStartDateQH)

and

Min(${<IsInqueue  = 1>} ContactStartDateQH)

The previous filter I created it the following way:

sum(

                              {$<

                                        ContactStartDateQH = {"= ContactStartDateQH < max({$<ContactStateID={4}>} ContactStartDateQH ) "}

                                        ,ContactStartDateQH = {"= ContactStartDateQH >= max({$<IsInQueue = {1}>} ContactStartDateQH ) "}

                                        ,IsInQueue = {1}

                              >}

                              ContactDurationQH

                    )

The wrapping it around an Aggr function, but the problem is that if I take each of the filters out, they behave as expected, the Max{$<>} get the max from the current group and so does the Min{$<>}, but when they are combined, it brakes.

I have tried to come up with some sort of

sum(

  {$<

  ContactStartDateQH = {">= Max{$<....>}ContactStartDateQH < Min({$<...>} ContactStartDateQH ) "}

  ,IsInQueue = {1}

  >}

  ContactDurationQH

  )

But I got 0 results back.

I am attaching a file that reflects 100% the scenario that I am working on if you want to play with it

Thanks in advance for any suggestions or comments about the best way to create this, dynamically grouped filter

[#2]

By the way, I tried to include another Expression with the following code:

concat({$<IsInqueue = {1}>}ContactDurationQH, ',')

To show the records that poteentially could be taken in consideration for the SUM, but the call is returning weirds results, since is not filtering correctly:

What could be the reason for this?

Regards,

-Ed

[lironbaram · Accepted solution]

hei

i changed a little bit your expressions formulas

have  a look

hope its helps you

[#4]

Thanks a lot Liron your suggestion definately helps, I just have one more question, inside the logic you have this:

ContactStartDateQH = {"<$(=max({$<ContactStateID={4}>} ContactStartDateQH )) >=$(= min({$<IsInQueue = {1}>} ContactStartDateQH )) "}

which means

ContactStartDateQH = < X >=X

That is just fine, but I was performing a fulltest with this logic and found a case where I needed it to be

ContactStartDateQH = <= X >=X

and then I changed it to be <=, but this broke the logic and now its giving wrong, results, how would you arrange the logic so both <= and >= are considered in the filter?

Thanks in advance for all the time, I really appreciate it.

-Ed

[lironbaram]

hei

i will explain what is not working is up to you to decide what to do

when you change the formula to

=

sum(
aggr(
//Expression
sum(
{$<          
ContactStartDateQH = {"<=$(=max({$<ContactStateID={4}>} ContactStartDateQH )) >=$(= min({$<IsInqueue = {1}>} ContactStartDateQH )) "}          ,
IsInqueue ={1}
>}
ContactDurationQH
)

//Dimensions
,ContactStartDateQH
,ContactID
)
)
nothing changes because of the second part of the set analysis

the      IsInqueue ={1} mean that it including only lines with the value 1 your max line has value 0 so it dosent chnge the resualt

if you remove the      IsInqueue ={1} from the expression it will work , but idont know is this what you want

i am attaching  an example with the new expressions

[#6]

You are right, maybe i did something else while testing it.

Thanks a lot, it works perfectly now.

-Ed