How to add filter for time range in a dashboard

[Sanket_Sharma]

I am having a column as c_time in my app, which includes the time value from a timestamp in HH::MM format.

I want to provide customer a dynamic functionality to view the data in any time range.

For ex: how many user where logged in between 11 to 11.30

[Taoufiq_Zarra]

can you share a sample data ?

[Sanket_Sharma]

Hi 

Sample data is provided here now what i want is a time range picker which can pick time in 30 min difference.

For ex- How many users were logged in 11:00-11:30.

[Taoufiq_Zarra]

you can use Intervalmatch
attached a qliksense proposal

the idea is to group users by intervalmatch and then count(distinct user)

I stopped in interval at 12:00:00 you can add the rest until 23:00:00 to finish the application.

Data:

LOAD * Inline [
    c_time, c_date, userID
    10:00:00, 01/07/2020, user1
    11:38:25, 01/07/2020, user1
    10:48:25, 01/07/2020, user2
    11:58:25, 01/07/2020, user2
    12:00:00, 01/07/2020, user3
];


Intervaltime:
LOAD * INLINE [
    Start, End, Interval
00:00:01,00:30:00,0
00:30:01,01:00:00,1
01:00:01,01:30:00,2
01:30:01,02:00:00,3
02:00:01,02:30:00,4
02:30:01,03:00:00,5
03:00:01,03:30:00,6
03:30:01,04:00:00,7
04:00:01,04:30:00,8
04:30:01,05:00:00,9
05:00:01,05:30:00,10
05:30:01,06:00:00,11
06:00:01,06:30:00,12
06:30:01,07:00:00,13
07:00:01,07:30:00,14
07:30:01,08:00:00,15
08:00:01,08:30:00,16
08:30:01,09:00:00,17
09:00:01,09:30:00,18
09:30:01,10:00:00,19
10:00:01,10:30:00,20
10:30:01,11:00:00,21
11:00:01,11:30:00,22
11:30:01,12:00:00,23

];


inner Join IntervalMatch ( c_time )
 
LOAD Start, End
Resident Intervaltime;

[Sanket_Sharma]

Hi Taoufiq,

I used the below script however the c_time is not getting loaded in intervaltime table due to which association is not getting created

Data:
LOAD

"USER ID",
"Query to BOT",
"Response From BOT",
"Bot name",
"Timestamp",
Time("Timestamp", 'hh:mm:ss') as c_time,
"Account Name",
"Not Responded / Liked / Disliked",
"Reason For Dislike",
"Intent ID",
"Dimension(user level)"
FROM [lib://AttachedFiles/Chatbot_10jan.xls]
(biff, embedded labels, table is Sheet1$);

Intervaltime:
LOAD * INLINE [
Start, End, Interval
00:00:01,00:30:00,0
00:30:01,01:00:00,1
01:00:01,01:30:00,2
01:30:01,02:00:00,3
02:00:01,02:30:00,4
02:30:01,03:00:00,5
03:00:01,03:30:00,6
03:30:01,04:00:00,7
04:00:01,04:30:00,8
04:30:01,05:00:00,9
05:00:01,05:30:00,10
05:30:01,06:00:00,11
06:00:01,06:30:00,12
06:30:01,07:00:00,13
07:00:01,07:30:00,14
07:30:01,08:00:00,15
08:00:01,08:30:00,16
08:30:01,09:00:00,17
09:00:01,09:30:00,18
09:30:01,10:00:00,19
10:00:01,10:30:00,20
10:30:01,11:00:00,21
11:00:01,11:30:00,22
11:30:01,12:00:00,23

];
Join
IntervalMatch( c_time )

LOAD Start, End
Resident Intervaltime;

Please find attached  error SS

[Taoufiq_Zarra]

Hi, what I've sent is an example that needs to be completed. in Intervaltime: I stopped at 12:00:00, you have to do the same until 23:59 (add the other 23 lines). if you still don't get a result, you can send me a data extract to see

[Sanket_Sharma]

Hi

Please find the attached document. I have added the time interval upto 23:59. But start and end fields are not showing the correct data corresponding to c_time. Kindly check and do the necessary action.

[Taoufiq_Zarra]

can you send me data other than a PDF to try to load it and see the error?

[Sanket_Sharma]

Please find the attachment.

[Taoufiq_Zarra · Accepted solution]

sometimes we don't know how 🙂 but the attached version works fine. in load Data: use: Maketime(Hour(Time("Timestamp",'hh:mm:ss')),Minute(Time("Timestamp",'hh:mm:ss')),Second(Time("Timestamp",'hh:mm:ss'))) as c_time,