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
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.
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
You can display max date in two steps:
Create a calculated field:
dateDiff(minOver(DateField, [], PRE_FILTER), DateField)
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