Question

Create Custom Filter for Time Frame if Date is in Field A, B, C, etc

  • 10 July 2018
  • 1 reply
  • 1075 views

Basically, rather than using a derived table and join statements, I am looking for a way to create a dimension group, customer filter or other way to create a look with a filterable date to show total people who entered each stage during a specific time frame regardless of when they were created in the system (since they could have entered the system in Q1, but not hit a stage until Q2). Each stage is a date field, so basically I need to show a table of total people in stage A, B, C, D if the date is within that time frame for date field A, date field B, etc. THANK YOU!



So, the SQL query would look something like below, but how would I set this up in Looker?



with Apps as


(select Owner, ‘1-New Apps’ as Stage, count(*) as Total


from TABLE


where date_new >to_date(‘2018-05-31’)


Group by Owner),



Recruiter_Review as


(select Owner, ‘2-Recruiter Review’ as Stage, count(*) as Total


from TABLE


where date_recruiter_review >to_date(‘2018-05-31’)


Group by Owner),



Sparkhire as


(select TA_Owner, ‘3-Sparkhire’ as Stage, count(*) as Total


from TABLE


where date_sparkhire >to_date(‘2018-05-31’)


Group by Owner)



Select NA.Owner, coalesce(NA.Total,0) as New_Apps, coalesce(RR.Total, 0) as Recruiter_Review, coalesce(S.Total, 0) as Sparkhire



from New_Apps NA full outer join Recruiter_review RR on NA.Owner=RR.Owner


full outer join Sparkhire S on NA.Owner=S.Owner


Order by Owner



#lookml #visualizations-and-dashboards


1 reply

Userlevel 2

Hey @lindzhg,



Philip from support here. I’m not totally clear on what your desired result set is, but if it’s just the count of all people who have records for a certain stage at a user specified time period grouped under a certain field, then there are few things you can do. For my example, I used an ecommerce dataset with records of users’ purchase per category. If I wanted to see the counts of orders per category for a specified timeframe, grouped by first name, I could do this:





filter: timeframe_filter {

type: date

}



dimension: in_time_frame {

type: yesno

sql: {% condition timeframe_filter %} ${orders.created_raw} {% endcondition %} ;;

}



measure: count_of_yes_in_timeframe {

type: count

filters: {

field: in_time_frame

value: "yes"

}

}





I used this table calc to clean up nulls:





Therefore, if you create a yesno dimension checking whether a given row’s date field(s) are/is within the specified timeframe, pivot by Stage, and group by owner, I imagine you can achieve something similar. Again, I’m not sure what result set you want, but go ahead and hop on chat or visit help.looker.com if you want to dig into this more.



Thanks,


Philip

Reply