How to visualize the change in value for the same unique ID across two dates?

How to build a visual in quicksight which shows the changes in the data between 2 dates (compare data within a same table). For example, consider the below data where “Server Name” is a unique identifier :

Execution Date Server Name Metallic Rating Deployment Status
31 Dec 2022 Server1 Gold Deployed
31 Dec 2022 Server2 Silver Not Deployed
03 Jan 2023 Server1 Platinum Deployed
03 Jan 2023 Server3 Gold Deployed

If you notice, between two dates, following has changed:

  • Metallic rating of Server 1 changed from Gold to Platinum
  • for the execution date of 03 Jan 2023, server2 record has been removed and a new record for server3 has been added

Using quicksight, I want to build a visual which shows:

  • (a) between two execution dates, the metallic rating for server1 changed
  • (b) new additions for the date of 03 Jan 2023

I tried this using different visuals but not able to find out how to do it. Any help will be much appreciated

Hi Aadi,

Can you elaborate more on your question. Can you achieve same using range filter on table visual as I have shown in below screenshot:

Hello Anwar,
Thanks for your inputs. I want to show the visual something like this

Name 31-Dec-22 3-Jan-23
Server 1 Gold Platinum

To some extent I am able to achieve it using “lag” function and I get output like this in quicksight

in this c_myexperiment is a calculated field as

lag(Environment,[Hostname ASC,{GDS Created ON} ASC],1,[Hostname])

Now the challenge I have is it is also showing the devices with hostname where value of “Environment” field has not changed. Is there a way I can only show or highlight the “hostnames” where the values are different ?

You can do something like this and then only filter or use conditional formatting for the highlighting.

ifelse(firstValue({environment},[{GDS Created ON} ASC],[{hostname}])={c_myexperiment},‘Do not show’,‘Show’)