CODEWITHSUNDEEP
amazon-web-servicesamazon-quicksight
n34_panda
n34_panda

Reputation: 2677

AWS Quicksight - MAX Data calculation but display date

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

Upvotes: 6

Views: 9860

Answers (3)

LowlyWorm
LowlyWorm

Reputation: 91

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

Upvotes: 2

DataNut
DataNut

Reputation: 317

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

Upvotes: 1

andrew citera
andrew citera

Reputation: 385

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.

Upvotes: 2

Related Questions