How to create a visualization to show count on day level

I have a table which has S3 access logs.
For example,

| Bucketname | Rolename | Operation | Requesttime        |
|------------|----------|-----------|--------------------|
| bucket1    | role1    | write     | 2022-10-01 10:15:00|
| bucket2    | role2    | read      | 2022-10-01 12:30:00|
| bucket1    | role3    | put       | 2022-10-02 14:45:00|
| bucket1    | role1    | write     | 2022-10-02 15:30:00|
| bucket2    | role2    | read      | 2022-10-03 08:45:00|
| bucket3    | role1    | put       | 2022-10-03 09:30:00|
| bucket1    | role1    | read      | 2022-10-03 11:15:00|
| bucket2    | role2    | write     | 2022-10-03 12:30:00|
| bucket3    | role1    | read      | 2022-10-04 13:45:00|
| bucket3    | role2    | put       | 2022-10-05 16:00:00|
| bucket3    | role1    | write     | 2022-10-05 17:30:00|

I want to create a table visualization where i display the count of the number of times a particular role accessed a particular bucket in the span of 30 days.
There can be multiple times in a day where a role might have accessed a bucket, but it will boiled to 1 transaction.
The output will look like:

| bucket_name | role_name | No.of time accessed |
|-------------|-----------|---------------------|
| bucket1     | role1     | 3                   |
| bucket1     | role3     | 1                   |
| bucket2     | role2     | 2                   |
| bucket3     | role1     | 3                   |
| bucket3     | role2     | 2                   |

The SQL to achieve this would look something like this:

SELECT
  Bucketname AS bucket_name,
  Rolename AS role_name,
  COUNT(DISTINCT DATE_TRUNC('day', Requesttime)) AS "No.of time accessed"
FROM
  your_table_name
GROUP BY
  Bucketname,
  Rolename

Can someone help me to achieve the same in quicksight.

@Prathik
ref: the screenshot…
Let me know if you still have questions

1 Like

Hi @Prathik, I think you want to create a calculated field using the distinctCountOver() function. The syntax would look something like:
distinctCountOver(truncDate(‘DD’, {Requesttime}), [{Bucketname}, {Rolename}], PRE_AGG)

Then, you would add bucketname, rolename, and this new field to a table visual. Lastly, you would apply a relative date filter to the visual, for the last 30 days. PRE_AGG functions are computed after filters, as specified here: Order of evaluation in Amazon QuickSight - Amazon QuickSight

1 Like