How to get maxOver calculation with timestamp precision up to seconds in QuickSight Direct Query?

Question & Answer
, , , , , , ,
1

Hi QuickSight Community,

I’m working on a level-aware calculation (LAC) in QuickSight to get the maximum timestamp for a field grouped by timeRange. The field contains timestamps updating each second, but when I use maxOver to get the maximum timestamp, I only get a result at midnight, like ‘Nov 6, 2024 12:00am’, instead of the correct timestamp, such as ‘Nov 6, 2024 7:49am’.

Additional Details:

  1. The timeRange field has values like 10_MINUTE, 30_MINUTE, 1_HOUR, 1_DAY, and 3_DAY.
  2. This LAC worked fine on a CSV file dataset using SPICE, but now I’m experiencing this issue with my current dataset which is RDS and using Direct Query.
    Here’s the expression I’m using for the LAC:
maxOver({field}, [timeRange], PRE_AGG)

Possibly Related to Direct Query: I noticed that when I use SPICE for the same RDS dataset, the calculation returns the correct output with timestamp precision, but in Direct Query mode, it appears to truncate the timestamp to just the date (no time). This makes me think it could be an issue specific to Direct Query truncating the DateTime data.

Has anyone encountered this before or found a way to make maxOver respect timestamp precision down to the second, especially in Direct Query mode? Any help would be much appreciated!

2

Hello @InfernoRider1 !

My assumption he is that if you just add that date field to a table in the same analysis that’s on direct query that you can get down to the second granularity. It’s possible that this could be related to direct query. Did you create the calculated field on the analysis layer or the dataset layer?

I would try using formatDate, something like formatDate({field}, 'yyyy-MM-dd HH:mm:ss'). Then use that field in your original calculated field rather than your date field.

Let me know what that gives you.

3

Hi @duncan ,

Using formatDate({field}, 'yyyy-MM-dd HH:mm:ss') converts the date to a string, which causes datatype issues since maxOver calculations cannot be performed on string data.

Additional context:

  1. The use case involves historical data in a database that updates every 5 minutes. To capture these updates on the dashboard, a Direct Query connection is required.

  2. The timestamp field is dependent on specific time ranges. I need the maximum timestamp value for each time range (e.g., 10 minutes, 1 hour) to accurately show message counts in my visuals for each time range. So, I am using maxOver to find max(Timestamp) which gives me latest counts of messages for the timeRange.

Let me know if you need further details to understand the problem!

4

Hi @InfernoRider1,
It’s been awhile since last communication on this thread. Are you still encountering the same issue or were you able to find a work around?

If still looking for a solution, it may be easier to assist if you are able to upload a copy of your analysis to the Arena View so that we can see how it’s returning values and further evaluate.

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

Thank you!

5

Hi @InfernoRider1,
Since we haven’t 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 and link this discussion for relevant information if needed.

Thank you!