0 Replies Latest reply: Jul 15, 2015 10:59 AM by Alain Konnu RSS

    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?