Knowledge Drop

# Creating a diff_quarter function in the Looker UI

• New Member
• 0 replies

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:

1. 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)))`
2. 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.
3. 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.