Dynamic Shift Status Display with Time Range Selection in QuickSight

[isingla]

I have a use case to implement in QuickSight. The dataset contains shift timings for associates, with StartTime and EndTime columns indicating the start and end of their shifts. Another column, Status , shows the status of each associate based on their shift timings. The objective is to display the status of associates in a dashboard for the current time. If the current time falls within any associate’s shift timings, their status should be displayed accordingly. Additionally, I want to add a parameter that allows users to select the data for different time intervals such as the last 1 minute, last 5 minutes, last 10 minutes, last 15 minutes, last 30 minutes, and last 1 hour. This will enable dynamic filtering of the data based on the selected time range.Do you have any idea how this can be implemented?

[Xclipse]

Hi @isingla, to implement this use case in QuickSight, you can follow these steps to create a dashboard that displays the status of associates based on their shift timings and allows for dynamic filtering based on the selected time interval.

1. Prepare Your Data:

• Ensure your dataset is properly formatted with columns for Date, Login, StartTime, EndTime, Manager, Ops, Sr. Manager, Status Type, and Status.

2. Data Preparation in QuickSight:

• Create a calculated field for the current timestamp.

Pseudocode (Syntax may vary)

current_time = now()

3. Create Calculated Fields for Time Ranges:

• For each time range, create calculated fields that will allow you to filter the data based on the current time. Here are examples for different time intervals:

Pseudocode (Syntax may vary)

last_1_minute = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('MI', -1, current_time), 1, 0)
last_5_minutes = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('MI', -5, current_time), 1, 0)
last_10_minutes = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('MI', -10, current_time), 1, 0)
last_15_minutes = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('MI', -15, current_time), 1, 0)
last_30_minutes = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('MI', -30, current_time), 1, 0)
last_1_hour = ifelse({StartTime} <= current_time and {EndTime} >= dateAdd('HR', -1, current_time), 1, 0)

4. Create Parameters for Time Intervals:

• In QuickSight, create a parameter to select the time interval.

Pseudocode (Syntax may vary)

time_interval_param = [‘Last 1 Minute’, ‘Last 5 Minutes’, ‘Last 10 Minutes’, ‘Last 15 Minutes’, ‘Last 30 Minutes’, ‘Last 1 Hour’]

5. Create a Control for the Parameter:

• Add a control (e.g., a drop-down list) to the dashboard to allow users to select the time interval.

6. Create Calculated Fields for Filtering Based on Parameter:

• Use the selected parameter value to filter the data dynamically. Create a calculated field that uses a case statement to check the parameter and apply the appropriate time range filter.

Pseudocode (Syntax may vary)

dynamic_filter = case
    when {time_interval_param} = 'Last 1 Minute' then {last_1_minute}
    when {time_interval_param} = 'Last 5 Minutes' then {last_5_minutes}
    when {time_interval_param} = 'Last 10 Minutes' then {last_10_minutes}
    when {time_interval_param} = 'Last 15 Minutes' then {last_15_minutes}
    when {time_interval_param} = 'Last 30 Minutes' then {last_30_minutes}
    when {time_interval_param} = 'Last 1 Hour' then {last_1_hour}
    else 0
end

7. Create Visuals for the Dashboard:

• Create visuals that display the status of associates. Use the dynamic_filter calculated field to filter the visuals.
• Example: A table or a bar chart that shows the associate’s status based on the current time and selected time interval.

8. Apply Filters to the Visuals:

• Apply a filter to the visual based on the dynamic_filter field to only show records where the value is 1 (i.e., the record falls within the selected time range).

By following these steps, you can create a dynamic dashboard in QuickSight that allows users to see the status of associates based on their shift timings and filter the data for different time intervals.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

Also, check out the list of QuickSight Functions and this forum:

Functions by category - Amazon QuickSight

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)