Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
In user's calculated dimension set the following expression:
Aggr(
sum({$<[Session Start] = {'>=$(=(vcPeriodBeginDate)) <=$(=(vcPeriodEndDate))'}>}[Session Duration]),
[User]
) <= Interval#('10:00:00')
,User)
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:
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
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
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
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
but that exactly what your expression those
put it in chart with out dimension and youll get the total result
This figure needs to be presented in a text box. and addition, part of the calculation is done in the Calculated dimension.
Dror
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
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