Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

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 ?)

111111
2221528
555155
17171711717
282828181122
81818118181
1221221221124124
1241241241127NULL
1271271271127127

Can someone explain me what I don't get?