How can I calculate a trailing average/median on top of a graph?

Some times the data is too ‘jagged’ and it makes sense to look at the trailing 7 day average / median.

Is there a way I can apply a ‘smoothing’ function on the result ?

Alternatively is there a way to find something like users created per day calculated weekly

I use the query builder to create the query.

1 Like

My first thought would be to calculate the average or median external to metabase and put the result in a table in your database that metabase points to. Although it would be great if you could just do it on the fly somehow.

are you using the query builder or sql queries?

I am using the query builder

@sheki — Did you find the answer to trailing / rolling / moving averages? Just strapped up Metabase to our DB and trying to do the exact same thing for our acquisition, activation, conversation, and retention rates. Will be working on it tonight and can update if I figure it out. I’m using the “question” interface, not SQL.

1 Like

Is this possible now (2019)?

@rviteri
There are some rolling periods in Smart Numbers:
https://metabase.com/docs/latest/users-guide/05-visualizing-results.html#smart-numbers
And I think @sbelak has been working on a much more advanced version (:window and sum-where) for the past few months, which might be included in 0.33 (please correct me if I’m wrong)

The windowing stuff will not be ready for .33, while sum-where & a couple of other new aggregations and custom expression functions will be.

As for the original question: we now have trendlines which in some cases might be good enough smoothing, else if you want something like weekly daily average, first create a question where you group by day, then a nested question off of that where you group the days by week and average the daily averages. Of course that will give you pseudo-smoothing only in the case of avg and distinct.

2 Likes

@flamber @sbelak it seems Smart Numbers are not available anymore, :window it not implemented yet (Custom Expression: Window functions · Issue #9393 · metabase/metabase · GitHub) and not sure if sum-where is ready and how to use it for creating trailing 12 months charts.

Would you guys be able to provide some guidance? Thanks you so much!