LinkedIn
Twitter
Knowledge Drop
Last tested: Mar 1, 2021
Unlike several other "diff" functions (example: diff_days, diff_hours, etc) that we offer in our Looker Functions and Operators doc, we don't have a native function to create diff_quarters! In the example below, we'll create a diff_quarter function between order_items.returned_quarter and orders.created_quarter.
Steps:
- First, we'll start by creating a diff_days_for_quarter function from the two quarters we've selected. We are extracting the months and years from each quarter and then turning that into a date. Once that's done, we are able to use the diff_days function and find the absolute value:
abs(diff_days(date(extract_years(${order_items.returned_quarter}), extract_months(${order_items.returned_quarter}), 01), date(extract_years(${orders.created_quarter}), extract_months(${orders.created_quarter}), 01))) - Next, we'll reference this diff_days_for_quarter function in another calculation, writing something like this:
round(${diff_days_for_quarter}/91.75,0)We are dividing by 91.75 because 365 days in a year/4 quarters gives us approximately 91.75. We then round to get to a whole number. - Finally, we can combine the logic and write something like this:
round((abs(diff_days(date(extract_years(${order_items.returned_quarter}), extract_months(${order_items.returned_quarter}), 01), date(extract_years(${orders.created_quarter}), extract_months(${orders.created_quarter}), 01)))) / 91.75,0)
We finally end up with something like this on our explore!
This content is subject to limited support.