Rounding 15min interval using datetime - Joining Datasets

Question & Answer
,
1

Hi folks. I have two separate datasets that i would like to Join via DataTime. Each dataset reports within a 15 minute window but each are off by a minute or two of each other. With no coherence in the minute the Join won’t work.

I want to create a calculated field for each Dataset that rounds the time to a 15 minute block, following @faisalchohan and @Max using this however I get the error (see red text image below) - using many variations of their calc snippet below. @Max solution is good within an Analysis but fails in Dataset preparation Joins.

Any way of tweaking this to use in Dataset editing and calculated field creation to Join datasets? Or can you provide an alternative calculation?

addDateTime(-(extract(‘MI’,{DateTime})%15),‘MI’,{DateTime})

image
image1480×215 17.6 KB

|Dateset 1||

|DateTime|Value|
|2024/06/01T12:01|20|
|2024/06/01T12:17|80|
|2024/06/01T12:35|30|
|2024/06/01T12:48|11|
|2024/06/01T13:02|65|
|2024/06/01T13:18|89|
|2024/06/01T13:36|41|
|2024/06/01T13:49|30|
|2024/06/01T14:03|22|
|2024/06/01T14:19|55|
|2024/06/01T14:37|68|
|2024/06/01T14:50|11|

|Dateset 2||
|DateTime|Value|
|2024/06/01T12:06|22|
|2024/06/01T12:15|55|
|2024/06/01T12:37|68|
|2024/06/01T12:50|11|
|2024/06/01T13:04|65|
|2024/06/01T13:16|89|
|2024/06/01T13:33|41|
|2024/06/01T13:46|30|
|2024/06/01T14:08|65|
|2024/06/01T14:16|89|
|2024/06/01T14:31|41|
|2024/06/01T14:46|30|

2

Hello @Rodger !

Are you trying to complete this calculation on the dataset that has already been joined? Also, this looks like QuickSight was able to save the calculation so is this error message appearing after you save your new calculated field?

The reason I ask is because I’m not sure that the calculated field is the only thing contributing to that error:

1 Like
3

Hi Duncan,

Thanks for the questions. To answer:

  • no the datasets contain no joins
  • yes the error is appearing after i save the calculated field

I created two new datasets using the previously shared data
The date fields we validated
The fields were: DateTime, value.

I created a Calculated Field within the Dataset. It saved, but displayed the following error (see image below).

The two datasets can be seen in the Arena Analysis - NOT joined.

Non Joined Datasets

Screenshot from 2024-06-25 18-03-35
Screenshot from 2024-06-25 18-03-351823×425 30.7 KB

4

Hello @Rodger !

Sorry to get back to you so late, I was OOO.

After re-reading your question, because you eventually want to join on a field where you round up or down to the closest 15 minute interval, I would recommend doing this in SQL prior to ingesting the data into QuickSight. Below is a a description of how to round date times using SQL:

5

Hello @Rodger !

Are you still working on this issue or have you found a solution? Again, I would recommend using SQL to round on your date time field and then completing the join.

6

Hi @duncan.

I am still working on this. I will hopefully have something to share very soon. Not SQL.

Thanks.

7

Hi @Rodger -

You can use this. Add to your datasets and use the dataprep join. I’ve tested and shared the Arena POC.

c_ds_datetime_15minute

parseDate(
    concat(
        toString(extract('YYYY', {DateTime})), '-',
        toString(extract('MM', {DateTime})), '-',
        toString(extract('DD', {DateTime})), ' ',
        toString(extract('HH', {DateTime})), ':',
        ifelse(
            floor(extract('MI', {DateTime}) / 15) * 15 = 0, '00',
            ifelse(
                floor(extract('MI', {DateTime}) / 15) * 15 = 15, '15',
                ifelse(
                    floor(extract('MI', {DateTime}) / 15) * 15 = 30, '30',
                    '45'
                )
            )
        ), ':00'
    ),
    'yyyy-MM-dd HH:mm:ss'
)

or

c_ds_datetime_15minute_mod

parseDate(
    concat(
        toString(extract('YYYY', {DateTime})), '-',
        toString(extract('MM', {DateTime})), '-',
        toString(extract('DD', {DateTime})), ' ',
        toString(extract('HH', {DateTime})), ':',
        toString(
            extract('MI', {DateTime}) - (round(extract('MI', {DateTime}) % 15))
        ), ':00'
    ),
    'yyyy-MM-dd HH:mm:ss'
)

Arena:
Rounding 15min interval using datetime - Joining Datasets

8

Hi @Rodger,
I’m just checking if the responses have helped and if you still need help on this question? If the previous response did help, please mark the reply as solution. If we do not hear back in the next 7 days, we will archive the question.
Many Thanks,
Andrew

9

Hi @robdhondt,

Thank you for the simple solution to my join issue. I can confirm the second offering (c_ds_datetime_15minute_mod) is working.

I am yet to test the first offering and selected the second due to its simplicity. Following testing of first, I aim to share my results.

In testing i can confirm:

  • I created a calculated field in each dataset using the robdhondt syntax
  • using the data prep ‘add data’ i added the second dataset
  • I Joined the datasets using the each calculated field - using the join type “inner”
  • The results - successful join - lets call it ‘15 minute data windows’ are created
  • Data within a 15 minute window is rounded down - example a data point at 15:13:21 is rounded to 15:00:00
  • If two data points are within a 15 minute window, the later is taken to be the sample - example sample 1) 15:01:33, and sample 2) 15:13:21. Sample 2 is joined and Sample 1 is dropped.

Working!

parseDate(
concat(
toString(extract(‘YYYY’, {DateTime})), ‘-’,
toString(extract(‘MM’, {DateTime})), ‘-’,
toString(extract(‘DD’, {DateTime})), ’ ',
toString(extract(‘HH’, {DateTime})), ‘:’,
toString(
extract(‘MI’, {DateTime}) - (round(extract(‘MI’, {DateTime}) % 15))
), ‘:00’
),
‘yyyy-MM-dd HH:mm:ss’
)

@robdhondt - thank you! Much appreciated.
Rodger

2 Likes