Time Grouping with Consistent YoY

Get Help
1

Hey,

Metabase has good support for time grouping and also with the Offset function.

However, is it possible to for example have consistent year over year offsets even when i have different time groups?

If i set offset to -12 for a month grouping then that wont work for a week grouping, etc. Or if i do daily grouping i’d want to see the same day last year.

2

I don’t think Metabase has “smart” grouping like this.

You may want to create some stock questions or models with built-in time grouping parameters so you can mix and match time groupings without having to write them from scratch every time.

There’s some useful advice in building year-over-year questions here:

3

Thanks @dwhitemv!

I don’t suppose you have an example of how this is best done?

I’m struggling here…

I have a table / view which I’ve created in my database warehouse. It contains all my data by day and site.

E.g.

site_id, date, total_revenue

All I want…is a nice pivot where i can see:

Period   |          Site 1            |         Site 2
--------------------------------------|--------------------------------
XXXXXXX  |  Current    |    Previous  |  Current    |   Previous   
--------------------------------------|--------------------------------
  1      |       A     |     B        |   C         |     D       
  2      |       E     |     F        |   G         |     H

Something like that.

I want users to be able to filter by date range, time group and filter by site_id.

The issues I face are:

If I use offset, the offset is wrong if i change time groups. i.e. if i offset by 12 for monthly time grouping, then changing it to week looks at 12 weeks back, instead of a year back.

I tried creating an SQL Question which had variables for those 2 filters & my time grain and had a current_revenue and previous_revenue. So it was like: Date, Site ID, Current, Previous

And it worked perfectly…as a table. I could bin by week/month/whatever and see it all correctly.

However…I can’t create a Pivot Table from it because Metabase thinks it isn’t an aggregated query.

I’ve tried just having the parent question give daily granularity and then grouping in a GUI query, but that doesn’t seem to work. It ends up using the exact previous dates in the previous year rather than the ISO week like I expect.