How to Align Multiple Metrics with Different Time Dimensions in Looker?

Looker Q&A Looker
1

Hi :waving_hand: ,

I need help defining a time dimension for a Looker chart where different metrics have different date fields.

Context

I am working with opportunities data, and I want to track three key metrics by week and by country:

  • opportunities_created → based on created_at date
  • opportunities_proposal_sent → based on proposal_sent_date
  • opportunities_won → based on won_date

Each metric is based on a different date field, but I want them to appear on the same chart to compare weekly performance.

Input Data Example

opportunity_id country created_at proposal_sent_date won_date
101 US 2024-03-01 2024-03-05 2024-03-10
102 US 2024-03-02 2024-03-06 NULL
103 UK 2024-03-04 2024-03-08 2024-03-12
104 UK 2024-03-07 NULL NULL

Expected Output (Aggregated Weekly View by Country)

Metric W1 W2 W3
opportunities_created 10 15 12
opportunities_proposal_sent 8 12 10
opportunities_won 5 7 6

Each metric should be counted based on its respective date field within the corresponding week.

Challenge

How do I define the weekly time dimension (W1, W2, W3) when each metric uses a different date field? I want to see those matrics on one table chart.

2

I don’t know if this possible with just that table. You need single unifying date dimension to get the week and group by to get those metrics. I would prob redesign that table so it’s one action per row and the date of that action. If you had that, you could group by the action(eg created, proposal sent, won) and pivot by action date and a count measure

1 Like