Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
DOD
Contributor III
Contributor III

Qlik Sense Pivot Table Timeline Chart

Hello,

I am working on creating a pivot table to display a timeline chart for users. The data is coming from a basic user log, where each user session has a LogIn and LogOut time.

I have a calendar created which records at 15 second intervals during each login session.

I have a pivot table in place which is coloured in blue if a user is logged in during a given hour, using the hour field as the dimension column. Likewise, I have another pivot showing the same, but based on the 15-second interval, with this as the dimension column. The measure is basically just counting the user ID, with conditional background colour applied. Both are shown 1st screenshot attached.

The goal however, is to display this with the hour as the dimension column, but have the measure calculate based on the smaller 15-second interval, so that it would show the relevant portions of the hour in each row, for when a user was logged in.

The 2nd screenshot attached is an example from Excel of how this would appear, with the portions of the hours coloured as per the smaller intervals.

Is this possible to do within the pivot table, by adjusting the measure but keeping the Hour field as the dimension column, any other way?

I have also attached a qvf with the current structure.

Thanks very much.

Labels (2)
4 Replies
DOD
Contributor III
Contributor III
Author

I'm just checking if anyone has any potential solutions for this.

 

Thanks again.

DOD
Contributor III
Contributor III
Author

Hello all,

Has anyone had a chance to review this or provide any possible solutions to the challenge?

Thanks very much.

Dalton_Ruer
Employee
Employee

If you have the data in some type of data model that has the user id's within the 15 minute block chunks, and that row already has the hour this should be no problem. Simply use Count(DISTINCT UserId) so that each user is only counted 1 time for the hour, despite the fact that they might be there 4 times. 

DOD
Contributor III
Contributor III
Author

Thanks for your response, Dalton.

Counting the user once per hour is not a problem, and is what you'll see in the top table of the first screenshot I shared in the original post, TimeLog Screenshot Pivot.

What I'm trying to achieve is to display the portions of the hours the users are logged in, by highlighting the 15 second intervals (i.e. counting the user in this interval), but displaying this under hourly column headers. This would show the portions of the hour highlighted, during which the user was logged in.

The TimeLog Screenshot , which is from an Excel file, shows how I want it to display.

Let me know if you have any ideas on how to achieve this.

Thanks again.