Getting the same day over multiple date intervals

bigquery
filter
lookml

(Diana Streche) #1

Chances are, that if you have a lot of data to analyze, either you or your users might have stumbled across the difficulty of getting a relative filter that compares the same day for this month, last month, 3 months ago, 2 years ago. Sure, you can add exact dates, but that means your Look/Dashboard will not move with time.

What do you do? Well, there are multiple ways of achieving this, I’m going to showcase a few here. These are tailored for BigQuery, but I think they can be adapted to whichever dialect you are using.

So far I’ve found 3 options that work:

  • BigQuery filter view
  • Custom Filter expression
  • Yes/No dimension filter + Matching filter

I’ll showcase each of them with pros and cons for each of them.

BigQuery Filter view

What I did when going for this option was create a view based on my sales table where I pre-aggregated some measures, then limited them over the days I needed them for.

SELECT
CAST(TIMESTAMP(FORMAT_TIMESTAMP(’%F %T’, orders.created_at , ‘America/New_York’)) AS DATE) AS orders_created_date,
orders.id,
SUM(table.revenue) AS revenue
FROM [Your_Table_Here] AS orders
WHERE 1=1
GROUP BY 1,2
HAVING
orders_created_date = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
OR orders_created_date = DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL 1 MONTH)
OR orders_created_date = DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL 2 MONTH)
OR orders_created_date = DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL 3 MONTH)
OR orders_created_date = DATE_SUB(DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY), INTERVAL 1 YEAR)

Then, as far as including it in the LookML, I simply made an explore out of it and included it in the dashboard where I want to put it:

explore: Same_Day_Revenues {
from: revenue_same_day
description: “Aggregated revenues for the same day over the past months”
}

Not very orthodox, but it got the job done.
What would I recommend this for?

  • If you’re SQL savvy and just need to get some figures out there
  • If you don’t want anyone else touching this bit of code

Cons:

  • It’s kinda dirty
  • Only manually extendable, you have to add a lot of code if you want more than the set intervals

Custom Filter

This is similar to what the BigQuery view does, but translating it into the language accepted by the Custom Filter field:

(extract_days(${orders.created_date}) = extract_days(now()) AND
extract_months(${orders.created_date}) = extract_months(now()) AND
extract_years(${orders.created_date}) = extract_years(now())
) current date
OR ( extract_days(${orders.created_date}) = extract_days(now()) AND
extract_months(${orders.created_date}) = extract_months(add_months(-1,now())) AND
extract_years(${orders.created_date}) = extract_years(now())) same day 1 month ago
OR ( extract_days(${orders.created_date}) = extract_days(now()) AND
extract_months(${orders.created_date}) = extract_months(add_months(-3,now())) AND
extract_years(${orders.created_date}) = extract_years(now())) same day 3 months ago
OR ( extract_days(${orders.created_date}) = extract_days(now()) AND
extract_months(${orders.created_date}) = extract_months(now()) AND
extract_years(${orders.created_date}) = extract_years(add_years(-1,now()))) same day 1 year ago

This can probably be made lighter than I made it here, since I’m not very savvy with Calculations/Custom filters. If you don’t want to bother with making it smaller, it will still work.

What I’d recommend this for:

  • On-the-fly analyses
  • If you want to get this yourself in the interface without having to bother a developer to get you this kind of filter

Cons:

  • Reaaaaaally ugly
  • Very specific (you need to specify the day, the month, and the year for each bit of the expression)

Yes/No dimension filter + Matching filter [my personal favorite from all of these options]

This one is honestly the best option in my opinion, even if you do need a bit of dev work on it. It’s really flexible and with minimal coding (just one LookML dimension) end users can select whichever time intervals they want.

The code looks like this:

dimension: current_date_over_months {
description: “Filters for the current day over the previous months”
hidden: no
type: yesno
sql: EXTRACT(day from DATETIME(orders.created_at, “America/New_York”)) = EXTRACT(day from DATETIME(CURRENT_TIMESTAMP, “America/New_York”));;
}

And when you want to use it, you simply add a filter on your date to set the intervals, then set the custom dimension to Yes:

This way, the user can set their own intervals, but can benefit from the filter without much hassle.

Important note One issue you need to be careful with when using these is if you are using timezone conversions between your database and your interface. Make sure all your functions line up for the same timezone :smiley:

Feel free to add to the topic if you know other methods of achieving this!


(sara.leon) #3

This is awesome! Thanks for sharing, Diana :slight_smile: