avarage time between actions

[Kalmer]

Hey!

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!
Kalmer

[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.

[Kalmer]

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

[Gysbert_Wassenaar]

You can add FIRST 10000 to each load statement.

From the help file:

First

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.

Syntax:

First n ( loadstatement | selectstatement )

Arguments:

Argument	Description
n	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.

Examples:

First 10 LOAD * from abc.csv;

First (1) SQL SELECT * from Orders;

[Kalmer]

Yes, as i mentioned i do know SQL and it was the first thing i did try but it didn't work. I managed to do it outside Qlik. I think it's a bug with MS access (since it does load data, but not the amount).

Ok here is the document.

Cheers!

[Kalmer]

Hey, managed to upload it to Qlik server (didn't notice the UPLOAD button). Maybe you have time to take a look.
The chart is on the first page and has a caption of "THIS IS THE ONE I NEED HELP WITH" - i don't think you can miss it .
Cheers!
So the problem here was that i have an issue with the avg time of "open" letter and "clicks".
I should count all the "open" letters which had "clicks" and make the calculation out of those opened letters.

Kalmer

[Gysbert_Wassenaar · Accepted solution]

You're treating the numbers your expression returns as times. They aren't. Change the format back to numbers and you'll see a large number that has a fraction of 0.3341...etc. If you treat that as a time that will be about one third of a full day, so close to 8 hours. What the number 16417 is I don't know, but I doubt it's the number of days between the clicks. Somewhere you'll have to change the action_timestamp values into real dates. Perhaps you need to divide the values by 86400 (=24*60*60).

[Kalmer]

Good answer and totally true. so i used to have this: " Date(floor(Makedate(1970,1,1)+ action_timestamp/24/60/60)) as Date," now im using this: "timestamp(action_timestamp/ 86400 + 25569) as TransactionDate,"

Cheers!