Filter a drill through dimension by a measure

I currently have a drill through dimension that is returning too many detailed records when you click on the drill through.  This is caused by not having a proper filter setup on the dimension so that it populates the drill through incorrectly.  The problem is the current dimension’s sql is subtracting a previously created measure from another. 

So I am attempting to recreate the measure by removing the subtraction of 2 measures.  In doing so i am running into a complicated issue that I am unsure how to resolve.  

The data I am working with tracks if a user is new or not.  If they are new the is_new_user field is flagged with a 1.  If they are a repeat user the is_repeat_user field gets flagged with a 1.  I want to only count repeat users.  So I need to disregard a user if they are new and repeat in the time frame that is selected.  

This query correctly counts the number of repeat users.

  select  userid
, sum(is_returning_user) as is_returning
, sum(is_new_user) as is_new
from USER_METRICS
where create_date between '2022-03-01' and '2022-03-31'
Group by user_id
having is_new = 0

In my LookML i have a is_returning dimension that simply points to the above is_returning_user field and a  is_new_user dimension that points to the above field.  The problem is I can’t simply filter by them since the is_new and is_repeating would occur on different lines

 

create_date userid is_returning is_new
7/1/2022 a 0 1
7/2/2022 a 1 0
7/3/2022 b 1  

In this example I would just want to count userid b

below is my current drill through lookml that doesn’t return the correct drill through information or count since the only filter is the returning user one.  It doesn’t take into account the is_new filter in the sql above.

measure: returning_users {
type: count_distinct
sql_distinct_key: ${TABLE}.userid ;;
label: "Repeat Users"
value_format_name: decimal_0
filters: {
field: is_returning_user
value: "1"
}
drill_fields: [
userid
, order_date
]
}



 

0 0 468
0 REPLIES 0
Top Labels in this Space
Top Solution Authors