Adding Last data refresh timestamp in the Dashboard

[Mounika]

Hi,
we have requirement to add the last data refresh timestamp in the dashboard. I am wondering if there is any way to have this as part of dashboard.

Thanks,
Mounika.

[seinchyi]

I’m assuming you are referring to SPICE refresh time?
Try adding a now() calculated field at the edit data step and this timestamp will be refreshed everytime SPICE refreshes.
You can then use this to display in your dashboard as SPICE last refresh date.

[Mounika]

Thanks @seinchyi ,This worked for me. I Appreciate your time in helping me out

[Kashgar]

Hi Mounika,

I’ve also encountered this question and I’ve also successfully used the above recommendation, but it doesn’t cover all my use cases. For example, when your SPICE dataset refreshes it doesn’t mean your data is current. I found that one of my redshift tables had a 18 hour lag due to other dependencies. When I used now(), it confused my customers who saw the hourly refresh time assuming all data was current as of that date & time when it only tells you how long ago the refresh was triggered.

If I want to see how current my data I recommend this:

Instead of using now() I recommend using max() and find a date column that works best. Say you are looking at a list of customer orders, you may look for a field like transaction_date or processed_date. In my case I was looking at a transcript for training. So I used the completed_date column.

One other tip, Instead of using Insight I prefer the WordCloud visual, since it keeps it centered and pretty. There might be a better way, but I prefer this. However, you have to CAST or CONVERT to CHAR. I use this:

,to_char(SELECT max(DATE) FROM TABLE),'YYYY-MM-DD HH12:MI PM') AS current_data_flag

Which ultimately appears in my word cloud visual like:

[Jesse]

In order to reference that field in your insight, did you need to add a Computation like ‘Maximum’ or something? I think that’s what I’ve had to do in the past. Add a metric and your ‘now’ field (which you added in data prep) to the insight, then add a Maximum computation, then you can insert the Maximum.timeValue,formattedValue variable into the narrative:

[Gal]

Hi,
could you explain to me where to add the now() calculated field in order to see when spice updates? i dont undertsand where to put it in the dataset or in the analysis

[Jesse]

Hey @Gal - you add this calculated field in your data prep screen (when editing a dataset). The entire formula is just ‘now()’, and can name it something like ‘last refresh date’.

[Goitom_Mehari]

Hi @Jesse

I am trying this today Dec.05.2022 at 5:40 pm PST. But the preview shows Dec 6, 2022.
Can you please help me with this.

Thanks!

[Jesse]

Hi @Goitom_Mehari - Be aware that now() returns the timestamp in UTC. You may want to add a calculated field and use addDateTime() to adjust the number of hours to be in your local time zone, then use this field instead.

[Goitom_Mehari]

Thank you, it worked!

[Kristin]

Thanks @Jesse! And thanks @Goitom_Mehari for circling back to let us know this is solved!

[saras]

Hi @Jesse,

Once the calculated field for now() is created, how do we include it in a narrative to display it? Is a specific insight required to be selected?

Thanks

[sagmukhe]

Hello @saras - Can you please try to use Maximum as the calculation and use that Refresh Time field to create the narrative. I would show up something like below. Hope this helps!

[AnwarAli]

Another option to get dashboard refresh datetime:

Using a lambda function API (using lists-data-sets) to query timestamp for dataset refresh and display it in custom visual of QuickSight. This lambda function is triggered on event of dashboard load via API gateway. You may pass dataset names used in dashboard as parameter to lambda function call. Sample code snippet and custom visual is depicted in screenshot below. If you implement it ensure that API call rate limit to list-data-sets in account are extended so that there shouldn’t be throttling.

import json
import boto3
from datetime import datetime

def lambda_handler(event, context):
client = boto3.client(‘quicksight’)
sourceAccountId=‘<<your-aws-account_id>>’
maxdate=‘’
dataset_response = client.list_data_sets(AwsAccountId=sourceAccountId)

names= event["queryStringParameters"]["datasetNames"].split(",")
print(names)

for  dataset in  dataset_response ['DataSetSummaries']:
        
        
           if dataset['Name'] in names:
               
                print( dataset['Name'] + " " + str(dataset['LastUpdatedTime']))
        
                if (maxdate==''):
                    
                    maxdate=dataset['LastUpdatedTime']
                    
                elif (maxdate>dataset['LastUpdatedTime']):
                    
                    maxdate=dataset['LastUpdatedTime']
                

return {
    'statusCode': 200,
    'body': json.dumps( str(maxdate))
}

[AnwarAli]

Another direct method is built-in last refreshed time for underlying datasets from dashboard. Dashboard shows last refresh time for all underlying datasets. Dashboard users can see it by clicking on datasource icon from right top of dashboard as shown in figure below.

[saras]

Hi @sagmukhe, it worked, I do get the correct date. I get the day but not the time though, is there a way to add the time as well?

In case other needs this: I also had to add the calculated field with now() to the time field of the Insight.

[Jesse]

@saras As long as you use the .formattedValue variable in your Insight, then when you set the format of that field either in the Field Well or from the field list itself, the format should be reflected.

[kawind]

Do yuo mean add a second calculated field? now() returned UTC time. but addDateTime() alone gives me an error

[Jesse]

Hi @kawind - you can do it all in one calculated field. In your dataset you would add a field like:
addDateTime(X, ‘HH’, now())

Where X is the hour adjustment from UTC.

[sbaskax]

@ Jesse,

In my dataset I have last updated week which is in integer, I am trying to populate “data available till : XX week”. I couldn’t able to do this, can you suggest me the way forward