Defining different measures on a common Calendar date dimension

[Manish_Ramrakhi]

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.

[cole_elliott]

👋 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:

• one for the UserId
• one for the UserActivity (that would hold all of your different user timestamps/milestones that you mentioned)
• one for the Timestamp

You could then either:

• add a single Measure that uses a parameter or templated filter to select one of your user timestamps/milestones and then composes the corresponding COUNT SQL via liquid appropriately
• define Measures for each of your user timestamps/milestones -> sounds like this may be what you need since you want to show all of the different counts by each user timestamp/milestone together on the same visualization

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 😁

[Manish_Ramrakhi]

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

[toddy86]

@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.