Qlik Sense Pivot Table Timeline Chart

[DOD]

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.

[DOD]

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

Thanks again.

[DOD]

Hello all,

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

Thanks very much.

[Dalton_Ruer]

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]

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.