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

[MarcMas]

Hi 👋,

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.

[schoolsofthough · Accepted solution]

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