6 Replies Latest reply: Jan 24, 2011 6:59 AM by Dror Svartzman RSS

    Set Analysis riddle

    Dror Svartzman

       

      To all of you Set Analysis wizards...

       

      I'm trying to achieve through a set analysis expression a second level of aggregation:

       

      Let me explain...

       

      Consider the following scenario:

       

      Sessions:

      LOAD * INLINE [

      User, Document, Session Start, Session End, Session Duration

      David, B,11-1-2011 18:32:00, 12-1-2011 20:00:45, 1:28:00

      David, A,1-9-2010 16:57:00, 2-9-2010 17:33:00, 0:43

      David, A,1-9-2010 23:57:00, 2-9-2010 0:00:00, 0:33

      David, A,1-10-2010 9:36:00, 2-10-2010 10:00:00, 0:24:00

      David, A,12-1-2011 7:48:00, 13-1-2011 9:52:00, 2:04:00

      Sandra, A,3-1-2011 8:48:00, 3-1-2011 10:01:00, 1:12:37

      Sandra, B,4-1-2011 18:32:00, 4-1-2011 20:45:00, 2:12:53

      Viktor, F,1-10-2010 9:36:00, 2-10-2010 10:00:00, 0:24:00

      Viktor, D,18-12-2010 20:55:00, 19-12-2010 1:55:00, 5:00:00

      Viktor, A,7-1-2011 7:00:00, 7-1-2011 8:00:00, 1:00:00

      ];

       

      1. Show in a Pivot table all the users, logged on in a given period with total session duration of 5 hours

        I've achieved this in two steps:

        1. In user's calculated dimension set the following expression:

      Aggr(

      sum({$<[Session Start] = {'>=$(=(vcPeriodBeginDate)) <=$(=(vcPeriodEndDate))'}>}[Session Duration]),

      [User]

      ) <= Interval#('10:00:00')

      ,User)

          1. In the 'Duration' chart expression set the following code:

            sum({$<[Session Start] = {'>=$(=(vcPeriodBeginDate)) <=$(=(vcPeriodEndDate))'}>}

      IF(

      Aggr(

      sum({$<[Session Start] = {'>=$(=(vcPeriodBeginDate)) <=$(=(vcPeriodEndDate))'}>}[Session Duration]),

      [User]

      ) < Interval#('5:00:00')

      ,[Session Duration]

      )

      )

      This gives the advisable results:

      error loading image

        1. In the second scenario I would like to show in a textbox the total session duration for the subset (2:40:37)

          Now this is where i'm stuck....

       

      I've attached a file as an example

      Your help is more than appreciated!

       

      Dror