Selecting Date Filter and Creating Previous Period

Hey everyone, I have a feeling this can be done since .36 but I am having a hard time figuring it out.

We have a dashboard with several questions on it, but only one question needs to have this Time Series/Period over Period layer comparison. So once the user selects from the Date Filter, we want to compare the selected time-frame that was chosen, for instance here is over several months AND days.

image

How can we have the time series line chart visualize the previous period with the current date filter selected. Something similar to:

select count(*) from “my_table” where “my_date” > (current_date - interval ‘2 week’) and “my_second_date” < (current_date - interval ‘2 week’);

It would be ideal if this could be done at an expression level using the built in builder, rather than using SQL as we would be able to spin these reports up faster. If not, how can it translated into metabase?

Thanks!

Hi @aschoettler
Currently Custom Expressions only handle dates in limited form, so I don’t think that would be possible.
https://github.com/metabase/metabase/issues/11330 - upvote by clicking :+1: on the first post

So you would have to do it in SQL, but you need to use two simple Date filters instead of a Field Filter, so you can get the start/end date of each and make a comparative query from those.

Or maybe I’m simply not understanding what you’re trying to do.

@flamber Would the date filters be associated with the selected date filter used globally on the dashboard, or are those start and end dates fixed?

The current date needs to be the one selected from (what I think is) the form field.

@aschoettler I’m 99% sure that I’m misunderstanding what you’re asking for.
It sounds like you’re simply just looking for variables in SQL, which can be connected to the dashboard filters:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
But again, I’m not sure what you’re asking for, so maybe it’s something else. Have a search around the forum, perhaps it’s just different words.

@flamber

OK I’ll try to reword it because I have searched the forums for this and I could not find it, and you stating that the custom expressions are limited (particularly with date) is worrisome.

I’ll do it from a use-case example.

  1. User is presented dashboard which has multiple filter types - date range, category1, category2
  2. The user selects the date range 06/01/2020 - 06/30/2020.
  3. All of the visuals change based on the selected date due to the “date” column being modified to review only the data within the selected date range.
  4. On a single widget/visual, it is a time series line chart to show “accounts open” each day over the course of the selected time period.
  5. THE REQUEST: - On that SAME time series line chart that shows “accounts open” each day over the course of the selected time period, we want to show a PREVIOUS PERIOD line that is within the same visual, so there can be a single source of Period over Period comparison, rather than having to go to the date range and reset the date to see the previous month.

What we wanted to do was to see if there is an easy way to do this via expressions and using the interval function to select the current_start_date -1 and current_end_date -1 so we can assess the changes over a selected period (not fixed to month, or week, but if someone chose a 4 day period, we’d want to see the previous 4 days).

Does this help?

@aschoettler Okay, I’m sure there’s several topics about that in the forum - I know because I have linked to these issues many times:
https://github.com/metabase/metabase/issues/5606
https://github.com/metabase/metabase/issues/5817
https://github.com/metabase/metabase/issues/8977
https://github.com/metabase/metabase/issues/9393
The only way to do what you’re looking for right now would be 2 Date filers (start and end), which you then make a time-shifted sub-query with.