topic Set Analysis riddle in QlikView

Set Analysis riddle

Mon, 24 Jan 2011 09:50:17 GMT

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

];

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

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

Set Analysis riddle

lironbaram — Mon, 24 Jan 2011 10:55:51 GMT

hei i dont understand whtat your problem if you take your expression and put it in a text box you are getting the total time of the users who qualify for the table so what is the problem exctly

Set Analysis riddle

Mon, 24 Jan 2011 11:09:26 GMT

Roi hi,

My problem is that i don''t know how obtain in a single expression, the TOTAL session duration of all users that were:

a. logged in in, let''s say, the last month and...

b. with total session duration of not more than 5:00

Dror

Set Analysis riddle

lironbaram — Mon, 24 Jan 2011 11:16:02 GMT

but that exactly what your expression those

put it in chart with out dimension and youll get the total result

Set Analysis riddle

Mon, 24 Jan 2011 11:21:09 GMT

This figure needs to be presented in a text box. and addition, part of the calculation is done in the Calculated dimension.

Dror

Set Analysis riddle

lironbaram — Mon, 24 Jan 2011 11:45:59 GMT

but why do you need the calculated dimmension

its acctualy the same condition as in the expression

remove the caculated dimmension from the table

and put the regular user dim

you'll get the same result

i am a liitle bit confused of your logic

can you explain why check the same conditon twice

Set Analysis riddle

Mon, 24 Jan 2011 11:59:37 GMT

Roi,

You're correct. I've mistakenly i've uploaded the wrong version. Setting the SA expression in a textbox does produce the correct results. THANKS!!

Toda rabba

Dror