Aggregate based on dynamic/relative timeframe

Question & Answer
,
1

Hi, I wanted to aggregate data in a custom manner where any row that is within a certain window is plotted on a single value in the x-axis.

For example, let’s say that we have 5 datapoints.
The first 2 are created on February 12. The next 2 are created on February 13. The last one is created on February 15.

I’d want any datapoints within 1 day of each other (the values are timestamped to a second of accuracy) to be grouped together. So, in this scenario, the first 4 data points would all be plotted against a February 12 value on the x-axis, while the final one would be plotted separately on February 15.

Is there any way I can achieve this by creating a calculated field?

2

Hi @hj98 and welcome to the QuickSight community!
My initial thought regarding this where you’ll run in to an issue is after you setup your date buckets, the calculated field will create a bucket for every date. So let’s say you create a bucket that starts on Jan. 12th and includes the next 2 days. The calculated field will automatically create a bucket for Jan. 13th (plus 2 days) and Jan. 14th (plus 2 days). It will not skip them because they are ‘included’ in a bucket.

While this would be quite tedious, you could create an ifelse statement that accounts for every date in the year:
Example:

ifelse({date_field = 'Jan 12, 2024' OR 'Jan 13, 2024' OR 'Jan 14, 2024', 'Jan 12, 2024',
          {date_field = 'Jan 15, 2024' OR 'Jan 16, 2024' OR 'Jan 17, 2024', 'Jan 15, 2024',
          .....,
          .....,
          NULL)

Otherwise, I believe your best option in this scenario would be to edit your dataset and create an extra field that has a bucket value. That way you can use this field in your visual instead.

3

Hi @Brett , thanks for your response.

So, from my understanding, the viable options that I have as per you are:

  1. Manual bucketization via bucket assignments through a calculated field
  2. Add some form of bucketization to my data source which can then be automatically used in the dataset analysis

On the example you shared, I actually would want that, as in, I would want all such entries that you mentioned to be grouped into a single bucket (if two days was my ‘window’)
However, from what you mentioned, it seems that there currently is no way then to go for the first approach in an automated way, right? i.e. dynamically/automatically bucketize all datapoints within 24 hours/1 day of each other.

4

Hi @hj98,
From an automated/dynamic way, that is correct. Currently there’s no QuickSight feature that easily sets up this type of bucket creation, if you were to setup a formula that checks if the date fell (let’s say, within 1 day…) it would only have the last datapoint to reference, so if you had more than two datapoints, it would not be able to programmatically group.

It would be nice for a feature to be available that can assist in bucket creation so I’ll mark this as a feature request.

Let me know if you have any additional questions

5

Hi @hj98,
While I’ve marked this as a feature request already, following up to see if you had any additional questions before I close out?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

6

Hi @hj98,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!