AWS Quicksight - MAX Data calculation but display date

Question

How Can I MAX a date but display it on the Analysis/Dashboard?

I am aware I can use the following to max a date:

extract('YYYY',OrderCreatedDate) * 365 + extract('MM',OrderCreatedDate) * 31 + extract('DD',OrderCreatedDate)

But how I can put it back into a Date Format to display to users, I want to display the "last" order date the customer placed.

Thanks

Answer 1

Based on my attempts it's not possible to calculate this as a single value except for tables and pivot tables. You can use the rank() function in a table or pivot table to calculate the most recent date and then filter on rank = 1 for the most recent date.

Alternatively, to achieve similar functionality, you can use a relative date filter and set dates relative to the current date time or from a parameter. For example, if you are ingesting data on a daily basis you can filter on 'this day' or 'X days' relative to the parameter.

The last resort would be to do a calculation in SQL before ingesting the data into QuickSight. You could create a view with a MAX() calculation and then join this to your dataset as a boolean column so you could filter on this within your visuals.