Announcements
This site is in read only until July 22 as we migrate to a new platform; refer to this community post for more details.

Defining different measures on a common Calendar date dimension

Hi,
I am a bit stuck in modelling my use case in looker in a good way.

So, I have a User explore which is based on a User View, lets say DimUser which has all different timestamps on various stages of a user.
For example : UserCreatedTime, UserSignupTime, UserPassedKycTime and so on. Have around 15 such different milestones of a users.
And then I have a measure on top of each, NumberOfUsersCreated, NumberOfUsersSignedUp, NumberofUsersPassedKYC etc. each measure is basically a Count with a CASE WHEN Corresponding_Dimension is NOT NULL.
Example :

View: DimUser
dimension: PassedKYC {
group_label: “User Activity Flags”
label: “Passed KYC”
description: “Yes if user passed KYC”
type: yesno
sql: CASE WHEN ${TABLE}.“PassedKYCTime” IS NOT NULL THEN TRUE ELSE FALSE END ;;
}
measure: NumberOfPassedKYC {
group_label: “User Counts”
label: “Number of Users Passed KYC”
description: “Number of Users that have Passed KYC check”
type: count_distinct
sql: CASE WHEN ${PassedKYC} THEN ${UserId} ELSE NULL END;;
}

Also,
This view DimUser is being used in lot of other explores as well.

Requirement:
I would like to bring in something like a CalendarDate/DimDate as a view in looker which is basically nothing but all possible dates and then I want to plot per calendar date, all above measures like NumberOfUsersCreated, NumberOfUsersSignedUp, NumberOfUsersPassed KYC so on and so forth.
My questions are :

  1. How should I join this DimDate with the DimUser (on which time dimension). Let’s say I join on all of them like DimDate.Date = UserCreatedTime or DimDate.Date = UserSignupTime or …
    This is how I am thinking of joining DimUser with DimDate
    join: DimDate {
    view_label: “Calendar Date”
    fields: [DimDate.iso_8601_day_date]
    type: left_outer
    relationship: many_to_one
    sql_on: ${Majority_DimUser.UserCreatedTime_date} = ${DimDate.iso_8601_day_date} OR
    ${Majority_DimUser.UserSignupTime_date} = ${DimDate.iso_8601_day_date} OR
    ${Majority_DimUser.PassedKYCTime_date} = ${DimDate.iso_8601_day_date};;
    }

  2. How do I define my measures in DimUser view. For example : Measure : NumberOfUsersCreated I can put a liquid variable to check if DimDate is in query then I can put a condition DimDate.Date = UserCreatedDate as well in the sql.
    This is how I tried doing it

measure: NumberOfPassedKYC {
group_label: “User Counts”
label: “Number of Users Passed KYC”
description: “Number of Users that have Passed KYC check”
type: count_distinct
sql: CASE WHEN ${PassedKYC}
{% if DimDate.in_query %}
AND ${UserPassedKYCTime} = ${DimDate.iso_8601_day_date}
{% endif %}
THEN ${UserId} ELSE NULL END;;

}

However, I still find a couple of problems with this approach and find it a bit messy to achieve this simple case.

  • Liquid variable is putting the check correctly based on DimDate is in the query or not, however it is not respecting the join conditions. So even when DimDate is not technically required in the query, looker is still putting DimDate in the join.
  • Since DimUser view is used in so many other explores.I have to put these DimDate joins everywhere, whrever DimUser is being joined and I actually do not want to pull in DimDate in those explores because they will simply cause confusion in those explores.

Any good ideas on how can I achieve this ?
Thank you in advance.

1 3 895
3 REPLIES 3
Top Labels in this Space