Help with Field Filters for Time Series comparing Year over Year

Get Help
1

So guys, I am pretty much new here, but I am trying to get a date Field Filter to get the same time frame on the current and previous year.

My intent here is to leave a trend quest with the entire year, with the possibility to filter for a month only.

This is the query to read the data:

SELECT makedate(year(date), 1) AS ref
     , SUM(propostals) as proposals
FROM insurance_sales
WHERE product <> 'VIDA FACELIFT'
  AND (channel = 'Mobile' OR channel='Internet')
  AND (
    makedate(year(date), 1) = makedate(year(now()), 1)
    OR date BETWEEN makedate(year(NOW())-1, 1) AND STR_TO_DATE(CONCAT(CAST(YEAR(NOW())-1 AS CHAR)," ",CAST(MONTH(NOW()) AS CHAR)," ",CAST(DAY(NOW()) AS CHAR)), "%Y %c %e")
      )
[[AND {{product}}]]
[[AND {{range}}]]
GROUP BY makedate(year(date), 1)
ORDER BY makedate(year(date), 1)

The problem is that the range filter set to “YEAR/MONTH” filter all the data and then I cannot compare the current year with the previous one…

2

Hi @Xukinorris
From the looks of it, you should be able to use Custom question instead of Native question:
https://www.metabase.com/blog/Time-Series-Comparisons/index.html

3

Hey, thanks for the answer. I was not able to see how doing this as a custom question solves the Field Filter problem… Or maybe I wasn’t clear enough on the problem I have.

I am using a “Trends” visualization, which brings the data from current year and shows how much it did improve from the data last year. The problem is that I wanted to be able to set a filter (that is already used for other visualizations on the same dashboard) to show this comparison for a single month, for example, I would set the filter to February/2020 and than I would see the amount of proposals sold on February and also see the comparison to same period of 2019.

But when I pick February/2020, it clears the data from 2019 and then it doesn’t shows the comparison.

4

@Xukinorris I don’t think you will be able to do that without a bit of complex querying.

Metabase will replace the Field Filter variable with something like this, when it’s a date range:

table.column BETWEEN '2020-02-01' AND '2020-02-29'

And that can be a little difficult to work with, so it’s probably easier just to use a single date instead, since you can manipulate it freely.

5

Yes, I figured that maybe I can create a view server side with a “Month” column that would solve this problem…