Adding Last data refresh timestamp in the Dashboard

Question & Answer
21

Hi @sbaskax if the week is an integer field, create an Insight and use the Maximum computation, then insert the formattedValue variable into your narrative (with whatever other text you want).

1 Like
22

image
image1920×1080 237 KB

@Jesse , my dataset don’t have date field, with out date field i couldn’t able to get the last updated week

23

@sbaskax you will need to add a calculated field in your dataset that uses the formula ‘now()’. See the screen Im talking about from the earlier post in the thread from Oct 2022: Adding Last data refresh timestamp in the Dashboard - #7 by Jesse

24

Hello @Jesse

Thanks for posting your solution.

The now() function is only giving the current time and it is not the exact time as the SPICE last refresh.

I understand that these both values can coincide when viewing the analysis/dashboard which refreshes at the same time making the values same. However, if we check at the dataset level itself, just refreshing the browser updates the now() value but not the SPICE refresh timestamp.

Therefore, I don’t think this is a reliable solution. Can you please correct me if I am wrong ?

25

Hi @SaiChema this will only work if you are using SPICE, and if so you need to add the now() calculation into the dataset, not in the analysis. When you add it in the dataset it gets materialized at the time of each refresh.

26

I added the now function, but the maximum values does not work

image
image794×140 8.45 KB

this is the narrative

image

27

Hi @remba87 - Which part are you referring to as not working? Is it that you are getting multiple timestamps? If so, are you using incremental refresh? This solution is assuming you are doing a full refresh. If doing a full refresh you should only have 1 value for the Now field (in your screenshot I see 4 different values). If using incremental, it will only refresh the values in the rows that were in the ‘lookback window’, which is what could result in different ‘frozen’ timestamps in that column.

If you are rather referring to the format of the displayed date, you can change the format on the field that is in your Field Wells in that Insight visual.

1 Like
28

Hi @Jesse,

Yes I am doing an incremental refresh…but still, should not I see the max values there anyway?

also, Max of a timestamp - Question & Answer - Amazon QuickSight Community

do you have any idea why if I have a minute aggregation I have one result and if I have the second it is another?

1 Like
29

Hi @remba87 - I replied over on your other question

30

If your underlying data source(s) are from Athena, you can also access the last update date and time of the table data in S3 with the hidden field $file_modified_time.

Right, to convert to, say, Eastern time in the us, something like this works well:

select
max(at_timezone("$file_modified_time", 'US/Eastern'))
from
table_name
1 Like
31

This is by far the easiest solution. Thank you for making this so simple compared to the other suggestions!

2 Likes
32

In my query I added CURRENTDATE() to retireve the time where it was last refreshed. In the insight I add | Latest refresh: Maximum.hasData = false Refresh Data Unavailable Maximum.hasData=trueMaximum.timeValue.formattedValue.
It works on the analysis, but when I publish the dashboard it doesnt retrieve the date, so it appears as Refresh Data Unavailable.
Why can I see the date in the analysis but not in the dashboard?
Thanks!!

1 Like