Skip to main content

Time zones

Jordan Rutner
  • Updated

Overview

Tableau stores data using UTC (Coordinated Universal Time) and does not provide time zone settings for the relative date filter. As a result, it might appear that some data is missing or tagged with the wrong date. Although KORE cannot change this limitation in Tableau, we do suggest two workarounds.

Option 1: Time adjustment parameter

Pros: Easier, uncluttered
Cons: Must manually change when DST begins or ends

  1. Create a parameter named “Time Adjustment” with the integer data type. Set its value to the UTC offset for your time zone. For example, the US Eastern time zone is UTC-4 during daylight saving time, so the integer value would be -4. When DST ends, change the value to -5 instead.
    image001.png

  2. Create a new calculated field named “Time Zone Adjustment”. Use the following calculation:
     DATEADD(‘hour’,[Time Adjustment],[Activity Date])
    image002.png

  3. Use the “Time Zone Adjustment” field instead of the default date field in your data source.

 

Option 2: Relative date filter

Pros: Automatically adjusts for DST
Cons: More complex, adds parameters to dashboard

  1. Create a parameter called “Time Period” with the string data type. Create the list of values with “Hour”, “Day”, “Week”, and “Month”. Set the current value to “Day”.
    image003.png

  2. Create another parameter called “Activities in last ‘N’ Period” with the integer data type. Set the current value to 2.
    image004.png

  3. Create a new calculation named “Activity Date - DST”:
    if [Date Filter] >= DATEADD('hour',2,(if DATEPART('weekday',DATEADD('month',2,DATETRUNC('year',[Date Filter]))) = 1
    then DATEADD('month',2,DATETRUNC('year',[Date Filter])) + 7
    ELSE DATETRUNC('week',DATEADD('month',2,DATETRUNC('year',[Date Filter])) + 13)
    end))
    and
    [Date Filter] <= DATEADD('hour',2,(if DATEPART('weekday',DATEADD('month',10,DATETRUNC('year',[Date Filter]))) = 1
    then DATEADD('month',10,DATETRUNC('year',[Date Filter])) + 7
    else DATETRUNC('week',DATEADD('month',10,DATETRUNC('year',[Date Filter])) + 6)
    end))
    then DATEADD('hour',1,[Date Filter])
    else [Date Filter]
    END
  4. Create a parameter named “Time Adjustment” with the integer data type. Set its value to the UTC offset for your time zone when DST is not in effect. For example, use -5 for the US Eastern time zone even during DST.
    image001.png

  5. Create a new calculated field named “Activity Date Adjustment”. The result of this calculation is a Boolean value, so bring it to the filters section and set it to “True.” 
    CASE [Time Period]
    WHEN 'Day' THEN DATEADD('day', - [Activities in Last 'N' Period], DATE(DATEADD('hour', [Time Adjustment], NOW()))) <= [Activity Date - DST]
    WHEN 'Hour' THEN DATEADD('hour', - [Activities in Last 'N' Period], DATE(DATEADD('hour', [Time Adjustment], NOW()))) <= [Activity Date - DST]
    WHEN 'Week' THEN DATEADD('week', - [Activities in Last 'N' Period], DATE(DATEADD('hour', [Time Adjustment], NOW()))) <= [Activity Date - DST]
    WHEN 'Month' THEN DATEADD('month', - [Activities in Last 'N' Period], DATE(DATEADD('hour', [Time Adjustment], NOW()))) <= [Activity Date - DST]
    END
  6. Display the two parameters on your dashboard. By removing the original relative date filter previously being used, you can now use these two parameters to display historical data more accurately. We recommend looking at the last 2 days to start and adjust as you see fit.
    image007.png