Getting Max_Date

Question & Answer
, ,
1

I have a column containing the formatted dates. How to get the max_date and subsequently display it in a word cloud / insight in my quicksight dashboard?

max(date) this does not work correctly

2

Hi @preyasi

You can try the following:
Let us say I have Invoice Date in my dataset.

  1. Create a calculated field Max Invoice Date to get the max value from your dataset
    maxOver(max({invoice_date}), [1])

  2. Create another calculated field Max Invoice Date Flag, that acts as a Flag to indicate a specific value in the dataset as Max
    ifelse(max({invoice_date})={Max Invoice Date}, 1, 0)

  3. Add the dataset field Invoice Date to create a visual. Filter the visual using the Max Invoice Date Flag and apply the filter value = 1. This will ensure that the Max date value from your dataset shows up in the visual

  4. Ensure that this visual does not get any other filters in your sheet applied.

2 Likes
3

How do you get the Max Refresh Time?

4

Hi @preyasi

Sorry! that was a typo. It should be Max Invoice Date that was created in the first step.

5

The Max Date flag now works but how to get the word cloud to only display the max date?

Screenshot 2024-08-29 at 15.14.11
Screenshot 2024-08-29 at 15.14.111442×922 77 KB

The Max date was Aug15 but as I grouped by week it shows Aug 11

1 Like
6

Include date field in Group by and aggregate by Day. Filter the visual by having Max Date Flag = 1 as the filter criteria. Size can be I guess on any field as it acts as a measure.

I haven’t tried this, but feel this should work

1 Like
7

This works! Thank you. Adding the filter worked.

8

It defaults to the start of the week when set to WEEK.