Creating a calculated dimension in script or in QlikSense & SetAnalysis incomprehension

Thu, 09 Jul 2015 10:33:11 GMT

Hello,

I am using Qlik Sense Desktop to explore statistics from what could be a website, particularly the connections and disconnections.

What I have is a mysql database in which are stored the logs this way:

id_log, id_user, _date (datestamp format: YYY/MM/DD hh:mm:ss), action (logIn or logOut)

What I am trying to do is to have the duration of the connections of the users, just as if there was an other colum, duration, calculated this way: if action is logIn, look the next rows until the action logOut with the same id_user, and do the _date difference.

(Actually it could be done for the row with a logOut action, but I don't think it matters).

Point is, I can't add this colum to my database, so I thought about two other solutions, but I'm not able to have either of them work.

Could you please help me?

Idea1: Create a field in the load script

Doesn't work basically because I have no idea how to do that. I woud need to request mysql values, make the calculation (the easiest part?) and finally write them into a dimension?

INLINE isn't the way I'm suppose to use I guess, and I can't find anyone else who has tried doing it.

Idea 2: Create the dimension in Qlik Sense

Require the use of the Aggr function because I obviously need to calculate around the _date dimension, I concluded that I should tried to create a measure first so...

Idea 3: Create the measure in Qlik Sense

I ha ve pretty much no idea why it doesn't work the way it should:

I create a table with _date, id_user and action (at this point it look likes the mysql table) then add a measure _duration whose formula is if(action='singIn', 1) -works fine- then puts if(action='singIn', Min(date_log)) (I'm progressing step by step) which results in _duration and _date beeing equal.

So I try if(action='singIn', Min({1} date_log)), which give the exact same result, which I don't understand...

Isn't Min({1} date_log) supposed to display the minimum value of date_log, no matter the conditions ???

-----------------------------------------------------------------------------------------------------------------------------------------------------

Since I am asking questions, here is one that in my opinion relates with my original problem but can be explained with few words:

If I have a dimension as below, how do I get the result I'm looking for ? :

_dimension	Min( _dimension)	Min({1} _dimension) (Why is it this ?)	actuel minimum (what I thought would result)	next value for even numbers, value for odd numbers (how could I do that ?)	next even value for even numbers, value for odd numbers (more difficult ?)
1	1	1	1	1	1
2	2	2	1	5	28
5	5	5	1	5	5
17	17	17	1	17	17
28	28	28	1	81	122
81	81	81	1	81	81
122	122	122	1	124	124
124	124	124	1	127	NULL
127	127	127	1	127	127

Can someone explain me what I don't get?

topic Creating a calculated dimension in script or in QlikSense & SetAnalysis incomprehension in App Development

Creating a calculated dimension in script or in QlikSense & SetAnalysis incomprehension