Consider the following sample table:

  day  |  payment_id  |  status
---------------------------------
 Jan 1 |      1       | processing
 Jan 1 |      2       | processing
 Jan 2 |      1       | completed
 Jan 2 |      2       | processing

As you can see, it consists of the day, payment_id, and status. Each payment can appear on multiple days with different statuses (e.g., payment 1 in the table below has different statuses on two different days).

What I need to do is to build a table in Looker Studio (Google Data Studio), which would be a simple stacked bar chart displaying the number of payments with different status over time. It’s easy to do this for each day, or for each week, but the problem arises when I need to create a chart where you would be able to switch between different time periods (days, weeks, months, etc.)

For example, for a daily chart, Jan 1 would show 2 payments with status processing, while Jan 2 would show 1 payment with status processing and 1 payment with status completed.

For a weekly chart, on the other hand, it would just show 1 payment with status processing and 1 payments with status completed for this whole week.

So it looks like for a weekly chart, I would need to take the latest status for each payment. Something like max_by function, but it doesn’t exist in looker studio.

Is it possible to create such a chart with multiple dimensions (days, weeks, months)? If so, how do I correctly aggregate the data?