How to get only the last records in quicksight?

user_3202406 0
Viewed 2411

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.

amazon-quicksight
2 Answers

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)
user_32024060

You can also add a filter on the visual, specifying the "top and bottom" option for the field you want to filter on, selecting how many top/bottom values you want to show, and then aggregating by MAX.

user_59090140
Related