How to display earliest/latest date/time in a QuickSight visual

Q&A
,
1

hello Quick Community,

I am having trouble doing a simple KPI visual in Quick Sight that display the latest refresh time from a calculated column.

I have a table in redshift called sales, which contains 4 rows of data and three columns, employee_id, employee_name and sales.

  1. I loaded sales into QuickSight dataset

  2. I added a Calculated Column called LatestRefreshTime using the now() function. the new column has been created with UTC time, the data type is Date.

  3. In a new Analysis, using the KPI visual, I dragged the LatestRefreshTime field into the VALUE field of the KPI visual, however there is only Count and Count (distinct) aggregation available, which means I can not get the time displayed since there is no max/min or earliest/latest aggregation for date data type

  4. As a workaround to this problem, I created a calculated field named LatestRefreshTime_Agg inside the Analysis, using max(LatestRefreshTime) to force a max aggregation, but when I add this calculated field, it gives me an error stating unsupported aggregation.

Please let me know what the correct way is of displaying the date field in a visual in QS

cheers.

image
image1697Ă—1685 232 KB

2

Hello @Benjamin_Blackswan, welcome to the Quick Community!

I would recommend using the calculated field in a simple insight narrative rather than a KPI. That will allow you to bypass the aggregation requirements for the field.

If you use it as a category field, then whatever random field as the value field, you can create a top 3 calculation in the insight (set it to only display 1 rather than 3), then you can import it into the narrative. You can write out the expression in the little box like this: Top.items[0].categoryValue.formattedValue

That should work in the normal expression editor! If you need any help, just let me know! The insights can be pretty fickle to edit.

3

no I want to avoid natural language summary.

I just want to create a simple visual that display the time in the column.

4

Hi @Benjamin_Blackswan, you don’t have to use natural language summary in an insight visual. You can just display the field and have it look like a KPI.

5

@Benjamin_Blackswan, here is an example: KPI Replaced by Insight Narrative

There are a ton of different calculations inside of the Insight visuals.

6

hello Benjamin,

My approach for this one was to create a table, hide the header, increase the cell height and it looked like a KPI visual.

7

hi Sergiu.Rotaru

Interesting workaround.

How can I display only one row in a Table Visual? Right now it is displaying all 4 rows.

image
image183Ă—129 2.88 KB

9

hi @Benjamin_Blackswan ,

Probably you’ve added the column in the Value field well. Just add it in the Group By. Group By groups by unique values.

10

Hi, @Benjamin_Blackswan Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Suite Community Q&A Forum!