Select and convert (apply) timezone (offset)

RoyBatty — Mon, 12 Aug 2024 15:38:09 GMT

Hi,

We have data where all timestamps are in the GMT+0 time zone.

We have simple data visualization: sheet with tables that, among other things, have columns that display the values of those timestamp fields.

We would like to have a filter-list of timezones or offsets (GMT+0, GMT+1, GMT+2 ... ) so that when you select one - to convert the timezone or apply an offset to those timestamp-fields (on for example, if you select GMT+8, then a -8 hour offset would be applied).

Is something like this possible in Qlik?

---------------------------------------------------

EDIT: Even this would suit us (if possible): For example, the user can enter a number somewhere and that number of hours is added to the existing values of the timestamp field.

Re: Select and convert (apply) timezone (offset)

nhenckel — Wed, 18 Dec 2024 03:57:48 GMT

Yes, this is definitely possible in Qlik Sense!

Step 1: Load the Timestamps Properly

Ensure your timestamps are loaded in the correct format. For example:

Data:
LOAD 
    Timestamp(TimestampField, 'YYYY-MM-DD hh:mm:ss') AS OriginalTimestamp
FROM [YourDataSource];

Step 2: Create a Variable for the Offset

Create a variable, e.g., vTimezoneOffset, with a default value of 0. This variable will represent the number of hours to offset from GMT+0.

Step 3: Adjust the Timestamp Dynamically

Use the following expression to calculate the adjusted timestamp:

=Timestamp(OriginalTimestamp + $(vTimezoneOffset)/24)

This adds the offset (in hours) to your OriginalTimestamp. For example, entering 2 in the offset variable will shift the timestamp by 2 hours.

Step 4: Add User Input

Option A: Direct Input

Add a Variable Input object to your sheet and link it to vTimezoneOffset.
Users can enter a custom number (e.g., 8 for GMT+8 or -5 for GMT-5), and the adjusted timestamp will update dynamically.

Option B: Predefined Timezones (Simplified)

Use a Variable Input object with the Dropdown option.
Define the dropdown options as:
- Name: GMT+0, Value: 0
- Name: GMT+1, Value: 1
- Name: GMT+2, Value: 2
- Name: GMT-1, Value: -1
- Name: GMT-8, Value: -8
Link it to vTimezoneOffset.

This allows users to select a timezone directly from a predefined list, and the adjusted timestamp will update accordingly.

topic Select and convert (apply) timezone (offset) in App Development