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

Transoceanic

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.

Strychnic

Good idea to filter a table to display a single value! – Kidskin

Answer 2

You can display max date in two steps:

1. Create a calculated field:

   dateDiff(minOver(DateField, [], PRE_FILTER), DateField)

2. Create an insight with DateField in Time cell and calculated field in Values cell. In customize narrative, add the following:

   Most Recent Date is Maximum.timeValue.formattedValue

Malcom

This is exactly answering the question and should be the accepted answer. – Yammer

Answer 3

Take a look at the maxIf() function. It sounds like it would do what you need. https://docs.aws.amazon.com/quicksight/latest/user/maxIf-function.html

The maxOver function would achieve your desired result. https://docs.aws.amazon.com/quicksight/latest/user/maxOver-function.html

This would allow you to determine the max value of the date field based off your specified data partitioning.

If the max date is always the last value for the customer, you could also use lastValue. https://docs.aws.amazon.com/quicksight/latest/user/lastValue-function.html

Enos