Creating new table with count of Users that had sessionDate between date and date-30

[#1]

Hi all,

I have 2 tables: user and sessions.

User
user_id
mostRecentSession_timestamp
user_created_timestamp

Sessions
session_id
session_date
user_id

Now I want to create a 3rd table, where I want every session_date and the count of active Users for this date. Active users for every date are defined by having a session at this date or in the 30 days before (so COUNT user if session_date between this session_date and this session_date - 30)

What my new table should look like:

session_date	# activeUsers
01.02.2015	20
02.02.2015	22
03.02.2015	39

How can I realize that in Qlikview?

Thank you!

Regards,

Anita

[Gysbert_Wassenaar]

What is this session_date ? I don't see that field in your example.

[#3]

Hi,

it is in the table sessions and it's a datefield, every User can have numerous sessions, so numerous lines in the table sessions.

Does that help?

Thanks

[#4]

Oh sorry I understood your question wrong,

this sessionDate doesn't exist, it is the session_date in the Sessions table.

In row 1 column 1 of the table I want to create there is the session_date 01.02.2015, so there should be the count of users that had a session_date between (01.02.2015 - 30days) and 01.02.2015.

Thanks!

[Gysbert_Wassenaar · Accepted solution]

Ah ok, I think I understand. You want to know for each session_date how many users had a session in the previous 29 nine days and that session_date. What you can do is create a new table with a new field:

AsOf:

LOAD session_date as report_date, session_date + iterno() -1 as session_date

RESIDENT Sessions

WHILE iterno() <= 30;

You can then use report_date as dimension and count(session_id) as expression.

Note, make sure your date fields contain numeric dates, not text strings. Use the date# function if necessary to turn text values into date values.

[#6]

Thank you very much, that works perfectly!