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
