Multiple times series with different X axis

Jbb
Explorer

I have two tables : `groups` and `messages`. To keep it simple let’s consider this :

677ee133-e450-4254-99fd-78a2d845c9dc.png

I am able to create two series showing : the number of total groups over time (thanks to a running_total measure) and the number of messages created (with a simple count).

What I’d like is to have those two series on the same plot. I created an explore joining the two views. I’m confused because I don’t know if this is possible on the first place.

While it visually looks similar, values are not the same.

93f637d2-14ea-4edf-a100-88986257b0cd.png

Should it be possible to make it work ? Is there a looker feature I’m missing here ?

Solved Solved
0 2 394
1 ACCEPTED SOLUTION

Dawid
Participant V

You would need to use a calendar table to make the dates agnostic. Right now you can either visualise it by group creation month or message creation month but in order to isolate them both.

You have some options:

  1. Create new explore with calendar as a base and then both groups and messages connecting to calendar by date
  2. Add two calendar joins to the existing explore, one joining to groups, one to messages and then in a view create a coalesce date from both of them (you could have a group created when no messages were created and vice versa - not talking about running total just yet)
  3. Create a PDT with the aggregation table
  4. Probably what I would do now in your case:  create calendar explore. Create a query with calendar, use merge query to add groups and messages, visualise the merge query.

View solution in original post

2 REPLIES 2

Dawid
Participant V

You would need to use a calendar table to make the dates agnostic. Right now you can either visualise it by group creation month or message creation month but in order to isolate them both.

You have some options:

  1. Create new explore with calendar as a base and then both groups and messages connecting to calendar by date
  2. Add two calendar joins to the existing explore, one joining to groups, one to messages and then in a view create a coalesce date from both of them (you could have a group created when no messages were created and vice versa - not talking about running total just yet)
  3. Create a PDT with the aggregation table
  4. Probably what I would do now in your case:  create calendar explore. Create a query with calendar, use merge query to add groups and messages, visualise the merge query.

Jbb
Explorer

I guess you’re right : I should use a calendar table and JOIN my resources tables to this table in the explore.

I eventually found and implemented another solution. I created a new view : union_group_message. This view has a derived table making the UNION between my 2 tables.

SELECT
creation_date,
group_id,
TRUE AS is_group,
FALSE AS is_message,
NULL AS message_id
FROM
`groups`
UNION ALL
SELECT
creation_date,
group_id,
FALSE AS is_group,
TRUE AS is_message,
message_id
FROM
messages`;;

The issue though is that I’ll have to maintain the message table at two places. With the calendar table solution I feel that I don’t have to duplicate my views thanks to JOINs in the model.

Top Labels in this Space
Top Solution Authors