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.
👋 hey @Manish_Ramrakhi!
If I’m being honest, I was lazy in reading your post such that I likely don’t understand it completely enough to begin to prescribe a holistic solution that will address your entire use-case, or each component of it.
However, I would suggest considering unflattening your DimUser view such that you end up with 3 dimensions:
You could then either:
What the unflattening attempts to alleviate is your need to have to join your DimDate view on each of the timestamp columns that you have in your current DimUser view with a column per user timestamp/milestone.
With this suggested approach, you could simply join DimDate to this unflattened view on the Timestamp column I described above.
Hopefully that is marginally helpful 😁
Thank you Cole for trying it out.
I solved it a bit differently though.
I added all my new measures in the DimDate view instead (Calendar Date) instead of adding them in User view. So i basically have two measures for each different Milestone of user.
Example:
One measure for simply counting number of users passed kyc and one for counting it per calendar date.
Its a bit complicated to explain it really but never mind, i think i have solved it in a good way
/Manish
@Manish_Ramrakhi ,
Whilst a couple of years on, I don’t suppose you have a worked example of how you implemented the above into your views (date + user) and explore?
I have the exact same use case.