Count closed and opened support cases per day

lookml

#1

Hello, I want to be able to show the number of support cases that were opened and closed on a given day.

In sql I’d probably write something like this:

with opens as (
select 
opened_date 
, count(*) as open_ct
from support_tickets
group by 1
  ) 
  
, close as (
  select 
  closed_date
  , count(*) as close_ct
  from support_tickets
  where closed_date >= coalesce(last_reopened_date, date('1999-01-01'))
  group by 1 
  ) 
  
select 
date_trunc('month', coalesce(opened_date, closed_date)) as month
, sum(close_ct) as closed_tickets
, sum(open_ct) as opened_tickets
from opens o 
	join close c 
		on o.opened_date = closed_date
group by 1

How would I write LookML so that I can get these results while also being able to change the date_trunc from month, week, etc.

Thank you for your help!


(Lauren Boltz) #2

Hi kkim!

A good place to start would be SQL Runner - from there you can query the SQL directly against your database to test the syntax and to make sure the results return as expected. Once you’ve tested the query, you can create a LookML view file and add it to your project by following the steps as outlined here

  1. Use SQL Runner to create a SQL query that you want to use for a derived table.
    Click Add to Project from the gear menu in the upper right.
  2. Select the project you want to add this derived table to.
  3. Enter a view name for the derived table.
  4. Click Add to add the query as a derived table in your project.
  5. Looker will switch to Development Mode (if not yet in development mode) and open the new derived table’s view in the selected project. If you selected a project that uses YAML-based LookML, Looker will provide the derived table in YAML-based LookML. If you selected a project that uses New LookML, Looker will provide the derived table in New LookML.

Alternatively to the SQL derived table outlined above, you can generate a Native (LookML-based) derived table from SQL runner with your SQL query:

  1. Use SQL Runner to double-check your query.
  2. From here, instead of Add to Project , click Get Derived Table LookML
  3. Copy the LookML from the window that pops up and manually paste it into your project, or click the add it to your project link to add the LookML to your project using the same procedure as above.

Once you have the new view file with your derived table, Looker automatically creates dimensions and measures to correspond with the table fields in the query. You can then join the table into your model as any other view!

One thing to keep in mind is that you cannot refer to LookML views, dimensions, and measures in the SQL of a SQL-based derived table.

Here is another helpful doc about derived tables, outlining the two different methods above (Native derived table vs. SQL derived table).

I hope this helps provide next steps - please feel free to contact support@looker.com with any questions you may have!

Lauren
Looker Support


#3

Hi Lauren!
Thank you for the quick reply! :slight_smile:

I want to add these measures of closed per day and opened per day into a View I have partially built out. That way my end users will be able to pivot on support ticket type or resolution type

And ultimately, I want my users to be able to drill down to the support ticket id level. If I use this query as a derived table, then I would lose the ability to go granular.

Is this something that’s logically not possible to create in a single View?

Thanks again!


(quinn.morrison) #4

Hi @kkim,

We will need to make this derived table its own view file. However, if we can join this derived table view file back to the explore that includes your original view file (i.e. on date) then we will be able to reference all of the fields in our original view file alongside our new, derived table-based fields. Another option is to utilize merged results to include fields from both views/explores, if we are unable to join in the model.

Another thing I wanted to mention, is the possible use of [parameters])(https://docs.looker.com/data-modeling/learning-lookml/templated-filters) to get that dynamic date_trunc you mentioned earlier. A parameter will appear as a filter-only field on the frontend, that will allow users to select from a set of allowed values. Whatever value the user selects will be passed directly into your sql. We could create something like this:

 parameter: date_granularity {
    allowed_value: {
      label: "Month"
      value: "month"
    }
    allowed_value: {
      label: "Week"
      value: "week"
    }
  }

And in our derived table, we can insert that parameter like this:

select 
date_trunc({% parameter date_granularity %}, coalesce(opened_date, closed_date)) as month

This will make the date_trunc granularity dynamic based on the value selected on the frontend.

I hope that helps!
Quinn


#5

Hi Quinn
Thanks for replying! :slight_smile:

I think you’re saying, I should make the query I shared above into a derived table and then join it back to the main explore on date.

That wouldn’t solve my problem since the opened_ct value will be repeated for every support ticket per date. It may be that this isn’t the kind of problem best solved by Looker…

I’ll keep thinking through this and if I come up with a solution that works, I’ll report back :slight_smile:

Thanks everyone!


(Michael Jalkio) #6

Hey @kkim I do think the merged results recommendation would be appropriate here (https://docs.looker.com/exploring-data/exploring-data/merged-results). Basically your users will create one explore showing opened ticket counts per day, a second explore showing closed ticket counts per day, and then merge those two on the shared dimension (date).