Quicksight Date Filter and Control

Question & Answer
,
1

Hi,

I am trying to display a timeseries that contains an as_of date and timestamp date. My dataset is as below

as_of timestamp_utc value
1-1-2025 01:00:00 1-1-2025 01:30:00 1
1-1-2025 01:00:00 1-1-2025 01:45:00 2
1-1-2025 01:00:00 1-1-2025 02:00:00 3
1-1-2025 01:15:00 1-1-2025 01:45:00 4
1-1-2025 01:15:00 1-1-2025 02:00:00 5
1-1-2025 01:15:00 1-1-2025 02:15:00 6
1-1-2025 01:30:00 1-1-2025 02:00:00 7
1-1-2025 01:30:00 1-1-2025 02:15:00 8
1-1-2025 01:30:00 1-1-2025 02:30:00 9

Relative to the current time, I want to display the values from the latest as_of that is available. For example,

  1. if current time is between 01:03 to 01:17, I want to show the rows with latest as_of 1:00
  2. if current time is between 01:18 to 01:32, I want to show the rows with latest as_of 1:15
  3. if current time is between 01:33 to 01:47, I want to show the rows with latest as_of 1:30

How do I fix this timestamp in quicksight? I have created a parameter predstart that is as below:

image
image702×795 35.7 KB

Then I created a calculated field:

ifelse(
    extract("MI", now()) < 3, addDateTime(-15, "MI", ${predstart}),
    extract("MI", now()) < 18, addDateTime(-extract("MI", now()), "MI", ${predstart}),
    extract("MI", now()) < 33, addDateTime(15 - extract("MI", now()), "MI", ${predstart}),
    extract("MI", now()) < 48, addDateTime(30 - extract("MI", now()), "MI", ${predstart}),
    addDateTime(45 - extract("MI", now()), "MI", ${predstart})
)

Is that correct? how do i then add this to the filters

2

Hi @avid_learner , thanks for your question.

Relative to the current time, I want to display the values from the latest as_of that is available. For example,

  1. if current time is between 01:03 to 01:17, I want to show the rows with latest as_of 1:00
  2. if current time is between 01:18 to 01:32, I want to show the rows with latest as_of 1:15
  3. if current time is between 01:33 to 01:47, I want to show the rows with latest as_of 1:30

Based on your desired outcome description, I have two ideas.

Note: the screenshots below are taken around Mar 5 00:09 UTC

Idea A: Create a calculated field flag based on the time diff between current time and the as_of timestamp. If the time diff is between 3 and 17, set it to “y” otherwise “n”. Use the calculated field to filter.

ifelse(
    dateDiff({as_of}, now(), "MI") >= 3 AND dateDiff({as_of}, now(), "MI") <= 17,
    "y",
    "n"
)

image
image1209×666 47.6 KB

Idea B: Use top N filter on as_of timestamp with minute granularity
If filtering to the most recent as_of timestamp based on the minute portion is good enough for your requirement, use the filter like following. But note that this always displays the rows with most recent timestamp, regardless of the 3-to-17 minutes trailing window.

image
image1122×762 48.7 KB

Please let me know if this helps.

3

@ytakahr, thanks for that. I went for your second solution for now because as you said, it allows me to just always show the rows with the most recent as_of regardless of what time it is.

Can I clarify what the logic of the filter is? why would the Integer be set to 1 Minutes in the yellow highlighted portion? Would it not be 15 minutes if the data granularity is 15 minutes? or does it not make a difference?

4

Thanks for your response, @avid_learner.

The “Minutes” in this case means the granularity. I know the UI is a little confusing, but it just mean the filter selects the top 1 record by comparing the as_of field at a minute granularity, not in the range of 1 minute.

I hope this clears your doubt.