Comparison of current period versus period year-1 with Date filter on Custom question

Hello,

I am using the Date Filter on a Dashboard with a custom question:

June and July 2021 are only given as an example, it can be any date.

image

My bar chart currently looks like this:

But I want to add a comparison with current date selected versus year-1 of date selected to achieve this kind of result:

Can you help me on this topic please? Thank you very much

1 Like

Hi @Novaska
Please read this: https://www.metabase.com/learn/questions/compare-times

1 Like

Thank you.

If I understand correctly. What I want to do is simply not possible? I can either:

  1. Compare 2 times series side by side on 2 different questions by using 2 different filters
  2. Merge the 2 time series in 1 question but then it has to be a static date range and I cannot use a filter

Did I understand it correctly?

@Novaska Because of a limitation with having the same field grouped multiple times, then you would have to do it in SQL if you want to do what you are trying to.

Is there a way to use a native query with the All options date filter shown here?

image

@Novaska Yes, https://www.metabase.com/learn/sql-questions/field-filters.html

1 Like

Working perfectly. Thanks :slight_smile:

@flamber

I made a native request and it uses the date filter as I wanted. However I did not manage to make the comparison as shown here :

The first link you sent mentions a specific way that "may" be what I am looking for. Quote " Now, if you want to compare dates relative to the current date, you can use the [ interval ] function in a [ case] statement, but we’re going to cover a different use case for interval next."

However it's not explained how to do it. I tried many things without success yet. Also I don't want to compare a date relative to "the current date" but relative to the date selected within the date filter.

Is this possible?

Thank you

@Novaska It would currently only be possible via SQL and with either a lookup table if you're using Field Filters, otherwise a simpler way would be to have two Date filters.

But there's 3 or 4 examples in the comparison article, so you can chose which one comes closest to what you need, but you cannot do exactly what you're asking.

@flamber

I made 2 date filters indeed and it allows me to get this result:

But it's not exactly what I am looking for.

When you say "It would currently only be possible via SQL and with either a lookup table if you're using Field Filters" did you mean that using a lookup table would help me to achieve my goal or I can only get something close, similar to the screenshot above?

@Novaska Yes, it would require several sub-selects to do what you're looking for, and you would need to return strings instead of dates, so it shows as an Ordinal instead of Timeseries on the axis, since Metabase will try to do auto-magic if it is a Timeseries.

1 Like