7 Replies Latest reply: Jan 15, 2016 3:03 AM by Kalmer Kaljuste RSS

    avarage time between actions

    Kalmer Kaljuste




      I work in a company which offers our product to send out emails to masses (something like mailchimp or sendsmaily). Anyway we have alot of actions going on (action = everything what happened with the client). Actions have different types and the most common ones are:
      Sent (email was sent to our clients), View (the client viewed the email letter/opened it), click (the client made a click to an url). I got really interested to see the avarage time what it takes for a client to make his/hers first click.
      So i built something like that:

      avg(Aggr(min({$<action_type={'click'}>}action_timestamp) - min({$<action_type={'view'}>}action_timestamp),action_email))

      I get an result but i think it's not right (i set the formating to simple time: 15:30:45), since i don't want to belive that it took 15 hours for clients to make their first click. So i'm pretty sure that it's also including the clients who just got the email and opened it (action_type = view)
      So what i really want to do is next (counting all the clicks made then subtract all views which had a click later).
      all(clicks) - all(views, but clicks=1). I think that way i can get an avarage time of the clients click when he/she opened the e-mail.


      Thank you in advance!

        • Re: avarage time between actions
          Gysbert Wassenaar

          It might be easier to calculate such intervals in the script. Perhaps you can post a small qlikview document with some example data that illustrates the problem.

            • Re: avarage time between actions
              Kalmer Kaljuste

              Hey Gysbert!
              Thanks for trying to help, but it's almost impossible to upload a huge document here. Since i'm only using 2% of my whole database which already has over 20 miljon rows it's difficult for me to find the location where i can set
              "LIMIT 10000" or something like that ( i know SQL but not very keen on Qlik Sense coding).
              TBH the code line was made to my by one of Qlik employees in Finland and he said that it's still a bit buggy, but he is using the correct fields to do that.


              Action timestamp right now has 365/366 generated values aka only dates, no minutes/hours/seconds. In my opinion i cannot even actually calculate avarage time in seconds/minutes if i've used the next expression:

              Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as Date


              and ofcourse the regular master calendar.
              I guess to fix the avarage time i should first fix this issue here: Problem with MasterTimeTable

                • Re: avarage time between actions
                  Gysbert Wassenaar

                  You can add FIRST 10000 to each load statement.

                  From the help file:


                  The First prefix to a LOAD or SELECT (SQL) statement is used for loading a set maximum number of records from a data source table.


                  First n ( loadstatement | selectstatement )



                  An arbitrary expression that evaluates to an integer indicating the maximum number of records to be read.

                  n can be enclosed in parentheses, like (n), but this is not required.


                  First 10 LOAD * from abc.csv;

                  First (1) SQL SELECT * from Orders;