How to display a date range in a footnote at the bottom of report?

[kbixler]

I have a dataset with two columns: data_source & data_date. These show the date that we last received data from for those particular data sources. There are many data_sources:

data_source	data_date
Source A	Sep 8, 2023
Source B	Aug 25, 2023
Source C	Sep 1, 2023
Source D	Aug 18, 2023

I have a requirement that the data_dates for two particular data sources show up in a text footnote at the bottom of a tab below all of the other visualizations. It can’t be a table, it has to be a text footnote with specific wording from the legal department that looks something like this:
“Lots of other text. Source: Source A as of Sep 8, 2023; Source B 26 weeks ending Aug 25, 2023. Some more text.” In the context of this report, I know that Source A will always be the maximum value and Source B will always be the minimum of the two.

I’ve tried using different variations of custom narratives, but I’m not having much luck. I’ve put my data_date column in the Time field well, and have left both the Values and Categories wells empty. Specifically, the Minimum.timeValue.formattedValue computation goes wrong when I add a filter on the dataset so that it only includes Source A and Source B. When I do that I get the Sep 8 date for both min and max

The source text for the narrative that almost works is:
“Sources: Source A as of Maximum.timeValue.formattedValue; Source B 26 weeks ending Minimum.timeValue.formattedValue.”

What I’m trying to do is similar to the answer to this question, but I want both max and min values:

Display Max(date) in a Text Box or KPI

I’m new to QS and this seems like it should be simple, but I can’t figure it out. So I have a column that has the TimeStamp the data was loaded into the snowflake warehouse my spice dataset loads from. I want to display a box at the top of my sheet that says “Data is current as of MM/DD/YYYY” where MM/DD/YYYY is the most recent date in the data set. I have a calculated field for Max Load Timestamp. It works in a Table view. But is there a place where I can put that in elsewhere? like in a KP…

As a work around, I thought about using a plain text box and parameters, but it’s unclear to me how I would get the min and max dates from my dataset into parameters.

Any tips on accomplishing the above by either using a custom narrative or a parameters with a text box would be much appreciated.

[apjvinod]

Hi @kbixler

Thanks for joining QuickSight community and posting questions.

In-order to effectively use Max & Min computation in the Insight Visual we need a measure to do the computation .

I have included additional column in the dataset to capture the “data_date” in a integer format and use it as Measure/value in the Insight Visual. (Include the hour/minute/second format if required)

Include date-integer field as values in the Insights visual and change the aggregation to Min/Max

And create Min / Max computation in the Insights and include formatted value to display .

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”.

[kbixler]

That works!

I had to slightly modify the calculated field because SPICE didn’t like the date format:
parseInt(left(formatDate({data_date},"yyyyMMdd'T'HHmmss"),8))