GUI Last agg function

1

Hi everyone,

I'm building a Model (cached question) in Metabase and need help with a specific aggregation.

I have a table with these columns:

  • year

  • month

  • week

  • start_week (date)

  • non-damaged ← the metric I need

  • removed

When I use the query builder → Summarize → group by year, month, week, Metabase only offers Sum, Average, Min, Max, Count, Cumulative Sum etc. for the non-damaged column. There is no Last / Latest value option.

What I need: for each year/month/week get the last (most recent) value of non-damaged, not the sum.

For the removed column I can use Sum - that works fine.

Here’s a screenshot of my data and exactly what I’m trying to achieve (I highlighted it in red):

image
image1368×562 54.4 KB

Is there any way to do “Last value per group” dirhttps://metabase.com/privacyectly in the GUI?

Any examples or best practices would be greatly appreciated!

Thanks in advance!

2

No, the Metabase query builder doesn’t support FIRST()/LAST() window functions. Write the query as a native query and you can call them, if your database engine supports it.

One workaround is to create a custom column with a constant 1 value, order the table how you want it, then create a cumulative sum of that constant 1 column, which creates a row number. Then create a filter node selecting the max value of that row number custom column.

1 Like
3

Thanks for the suggestion! The custom row-number column workaround could work, but I have a complication:

I need three different granularities in one dashboard:

  • weekly - value of that week
  • monthly - value of the last week of the month
  • yearly - value of the last week of the year

So I'd need two flag columns (is_last_week_of_month, is_last_week_of_year) and three separate saved questions — one per granularity?

4

If you are stuck on using query builder, then yes its easier to manage that way. The latter 2 build on the first one.

1 Like