amazon-quicksight

How to get only the last records in quicksight?

I have a dataset composed by something like this id<String>, date<Date>, status<String> filled with data like this:

+-------+------------------+----------------+
|   id  |       date       |     status     |
+-------|------------------|----------------+
|   1   | 25/05/2020 10:30 |      OPEN      |
+-------|------------------|----------------+
|   2   | 25/05/2020 10:31 |      OPEN      |
+-------|------------------|----------------+
|   3   | 25/05/2020 10:32 |      OPEN      |
+-------|------------------|----------------+
|   1   | 25/05/2020 12:30 |      CLOSED    |
+-------+------------------+----------------+

I want to have a visual that shows me the count of status, but geeting only the actual status of each id, in this example the visual should be formmed by this data:

+-------+------------------+----------------+
|   id  |       date       |     status     |
+-------|------------------|----------------+
|   2   | 25/05/2020 10:31 |      OPEN      |
+-------|------------------|----------------+
|   3   | 25/05/2020 10:32 |      OPEN      |
+-------|------------------|----------------+
|   1   | 25/05/2020 12:30 |      CLOSED    |
+-------+------------------+----------------+

I have tried using the count Distinct for the id, but it shows 3 OPENs and 1 CLOSED and I need a 2 OPEN 1 CLOSED result.

I also tried to make a Date filter using the TOP or Botton option, but this filters the data to the day with more records..

So, is there any way to do this?

Thanks in advance.

Solution

  • OK, 6 months later I can respond myself.

    What I have made is an ifElse combined with the maxOver of the date, taking the status in case of true or null in the else.

    ifelse(Date=maxOver(Date,[id],PRE_FILTER),status,null)