Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

0 Replies