Denominator should only consider the roles considered for the numerator in ratio calculations
Hi All, My data table will look like this Candidate Name Status Recruiter Full Name Job Ad Published date Timestamp Job Role Candidate 1 New PB 01/01/2020 02/01/2020 Support Worker Candidate 1 Phone screened KW 01/01/2020 05/01/2020 Support Worker Candidate 1 Interview complete PB 01/01/2020 24/01/2020 Support Worker Candidate 1 Offered role KW 01/01/2020 22/02/2020 Support Worker Candidate 2 New PB 01/01/2020 03/01/2020 Admin Officer Candidate 2 Phone screened KW 01/01/2020 05/01/2020 Admin Officer Candidate 2 Interview complete PB 01/01/2020 25/01/2020 Admin Officer Candidate 2 Offered role PB 01/01/2020 30/01/2020 Admin Officer Candidate 3 New PB 01/01/2020 03/01/2020 Finance Officer Candidate 3 Phone screened KW 01/01/2020 05/01/2020 Finance Officer Candidate 3 Interview complete PB 01/01/2020 25/01/2020 Finance Officer Candidate 3 Offered role KW 01/01/2020 30/01/2020 Finance Officer Conv.Rate should be= Candidates with offered Roles #(all roles offered within the current month)/Candidates with Interviews completed#(only for the offered positions in the numerator) I created a date table based on the max and min of the dates job was offered The result should be like this : There are 3 roles A, B,C advertised Month Offered Role Interview Complete Offered role/int complete- per month- expected result September - 5 for role A, 5/B 0 October 1/A 6/A, 6/C 1/11 November 1/A, 2/B 7/A, 7/B 3/30-denominator only considers the roles considered for the numerator Appreciate your help on this. Thanks,