Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

how to create Time Bucket with different time zone.

I have requirement from client.

We are have different Time Zones (5:00 UTC,-3:00 UTC,+5:00 UTC,4:00 UTC)

We are received data and completed date with time stamp as an IST Zone(+5:30 UTC) Within 24hours.

I need to Convert into Different Zone.

Report Should Show Delivered before 07:00 AM,Delivered between 07:00 AM and 05:00 PM and Above 05:00 PM for  Different Time Zone.

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

I've never known how to import a Timestamp  WITH timezone into Qlik.  Most of my work has concerned US East stuff.  But assuming you can get the date translated into UTC, then something like the following script should work.

SomeTable:

LOAD *,

IF( Hour(Plus5) < 7 , 1 ) AS 'Plus5Before7',

    IF( Hour(Plus5) > 7 AND Hour(Plus5) < 17 , 1 ) AS 'Plus5After7Before5'

;

LOAD *,

ConvertToLocalTime(deliveryDateUTC, 'UTC+05:00') AS 'Plus5',

    ConvertToLocalTime(deliveryDateUTC, 'UTC-03:00') AS 'Minus3'

;

LOAD * Inline

[

orderId, deliveryDateUTC

    1, 2018-02-22 09:23:33

    2, 2018-01-02 03:00:54

    3, 2017-11-04 10:13:21

    4, 2018-04-30 14:26:51

    5, 2017-10-31 08:36:57

    6, 2018-03-17 13:11:11

    7, 2018-05-02 01:31:48

    8, 2018-03-05 01:48:30

    9, 2018-05-30 11:17:07

    10, 2017-10-14 11:39:48

    11, 2018-04-23 01:43:57

    12, 2017-12-24 01:50:54

]

;

Here you can see me converting the times to the timezones of interest, and then I bucket them.  That will make Set Analysis less obnoxious.

View solution in original post

2 Replies
Highlighted
Specialist II
Specialist II

I've never known how to import a Timestamp  WITH timezone into Qlik.  Most of my work has concerned US East stuff.  But assuming you can get the date translated into UTC, then something like the following script should work.

SomeTable:

LOAD *,

IF( Hour(Plus5) < 7 , 1 ) AS 'Plus5Before7',

    IF( Hour(Plus5) > 7 AND Hour(Plus5) < 17 , 1 ) AS 'Plus5After7Before5'

;

LOAD *,

ConvertToLocalTime(deliveryDateUTC, 'UTC+05:00') AS 'Plus5',

    ConvertToLocalTime(deliveryDateUTC, 'UTC-03:00') AS 'Minus3'

;

LOAD * Inline

[

orderId, deliveryDateUTC

    1, 2018-02-22 09:23:33

    2, 2018-01-02 03:00:54

    3, 2017-11-04 10:13:21

    4, 2018-04-30 14:26:51

    5, 2017-10-31 08:36:57

    6, 2018-03-17 13:11:11

    7, 2018-05-02 01:31:48

    8, 2018-03-05 01:48:30

    9, 2018-05-30 11:17:07

    10, 2017-10-14 11:39:48

    11, 2018-04-23 01:43:57

    12, 2017-12-24 01:50:54

]

;

Here you can see me converting the times to the timezones of interest, and then I bucket them.  That will make Set Analysis less obnoxious.

View solution in original post

Highlighted
Creator
Creator

Thank You For Immediate Reply Justin Dallas.

I hope this will work fine.

I need to Check the Same day of received date and completed date.