Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.201520
02.02.201522
03.02.201539

How can I realize that in Qlikview?

Thank you!

Regards,

Anita

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
Not applicable
Author

Thank you very much, that works perfectly!