5 Replies Latest reply: Aug 23, 2011 10:32 AM by esepulveda RSS

    Help with Set analysis - Part II

      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)

       

      Duration1.png

       

      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

        • Help with Set analysis - Part II

          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:

           

          Duration2.png

           

          What could be the reason for this?

           

          Regards,

           

          -Ed

            • Re: Help with Set analysis - Part II
              Liron Baram

              hei

               

              i changed a little bit your expressions formulas

              have  a look

              hope its helps you

                • Re: Help with Set analysis - Part II

                  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

                    • Re: Help with Set analysis - Part II
                      Liron Baram
                      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