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 :
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};;
}
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.
Any good ideas on how can I achieve this ?
Thank you in advance.