Adding refresh date time stamp in custom narrative

Question & Answer
1

Hello, I am trying to add a “refresh date” calculated field in the custom narrative in quicksight, but it’s not displaying the correct date!

  1. i have embedded the desired date column in my custom query.
  2. i dragged the date column to the field wells on the top the page.
  3. I applied the right computation methods in the custom narrative editor box: Maximum.timeValue.formattedValue Then it’s showing the wrong date, not the latest(max) date of the desired date column. Can someone help me understand where am i missing?
    Great thanks!! Rosie
2

Hi @rosiebai, when you say you embedded the desired date column, how did you do that? I have created a calculated field and set the value to now() in my data preparation area. The computation type Maximum should work. What is the date that you’re seeing, compared to what you expect to see? Is that a valid date anywhere in your dataset? Can you also check the date aggregation is set to day in your field wells?

3

Hi Steph,

  1. i meant the ‘refresh date’ column is valid in my dataset, i just need to use max() function on this column to show the most recent date in the custom narrative text area.
  2. now() is not necessarily the max(refresh) that i am looking for. So it’s not applicable in my situation
  3. yes i have dragged the date column to my field wells on the top
    And the displayed date is not correct (i.e. it shows an old date from 2022, which is different from the most recent date when we refresh the data. )
    Any clues what might be going on here?
    Thanks,
    Rosie
4

Hi @rosiebai, understood, I misunderstood that you had different values in that field.
Try this:
Create a calculated field for MaxDate as maxOver({Refresh Date},,PRE_FILTER).
You can then use that MaxDate field in the Insight and it will give you the right value.
Hope that helps!

5

Hi Steph,
I don’t think i understand your solution. first, what is the square in between the two commas? second, why chose maxOver() instead of Max()? I don’t need to max over any other variable here. Third, i don’t want to add additional calc field to the dataset, what i need is only to display the max of the refresh_date column in the custom narrative.
Looking forward to hearing more from you :slight_smile:
Thanks,
Rosie

6

Hi @rosiebai, this is the calculation that will get that for you. the maxOver function takes three arguments, the square brackets are for partitioning, and since you want to use the whole dataset you can supply that as empty. The PRE_FILTER will mean the calculation will run over the whole dataset regardless of any filters you apply. I have tested with max and got the same result as you, maxOver is what works. Please try it :slight_smile: