Grouping Candidate Ethnicity RCOUNT formula not working

Ameya_J

Hi!

I am working towards building a OTBI candidate diversity report by application stage. In the report, for any applicant who selects more than 1 race ethnicity, i want to recategorize value to "Two or more races"

Trying to use formula proposed by @Raghavendra Raghunath-Oracle in one of the oracle sessions.
CASE WHEN RCOUNT("Job Application - Legislative Information"."Regulatory Response Ethnicity" BY "Candidate Details"."Candidate Number")>1 THEN 'Two or more races' ELSE "Job Application - Legislative Information"."Regulatory Response Ethnicity" END

But instead of single row, I am getting two rows. Please see below example with dummy values. Can someone please help

Nathan CCC

Hi Ameya,

For any aggregate function like sum count min max avg then using BY in function in your select clause will not change how many rows you get. To do that you need will need to aggregate across a "yellow" measure column or include a GROUP BY. The problem here is that the formula is based on 2 "blue" attribute columns in a case rather than function FILTER on a "yellow" measure column with a USING clause. The number of rows you get is determined by the blue attribute columns that you include in your view. We do not know what is the rest of your your query to help. If you post your logical SQL from your advanced tab of your analysis for the "simplest" query to demonstrate the issue I can help more. Do not add an attachment because many readers will be unable to download dur to company security policy regards the risk of downloading files from strangers on internet forums. Just post as text.

Assume this is your test case (old skool "Ask Tom" style)

if this is your data

Candidate, Ethnicity

A, X

A, Y

B, X

then you expect query results with 2 rows

"Candidate", "My Calculation"

"A", "Two or more ethnicity"

"B", "X"

?

Nathan

Nathan CCC

Hi,

Here is I think a solution

NOTE I used count distinct rather than a running aggregate. I used ethnicity code since it is more likely unique just in case 2 ethnicity have the same name. I included a fact since all queries must have a fact in them if you have selected multiple dimensions. If you include ethnicity as column you get bottom table. if you exclude you get top. You can include ethnicity as a column in your analysis but only exclude in the table view to get the 2 tables side by side if you want like I have in the screen shot above with the second summary table.

select all 0 s_0

, (count(distinct "Recruiting - Recruiting Real Time"."Job Application - Legislative Information"."Regulatory Response Ethnicity Code" by "Recruiting - Recruiting Real Time"."Candidate Details"."Candidate Number")) as n_ethnicity

, "Candidate Details"."Candidate Number" as Candidate_Number

, (case when ((count("Recruiting - Recruiting Real Time"."Job Application - Legislative Information"."Regulatory Response Ethnicity Code" by "Recruiting - Recruiting Real Time"."Candidate Details"."Candidate Number"))>1) then 'Multiple' else "Recruiting - Recruiting Real Time"."Job Application - Legislative Information"."Regulatory Response Ethnicity" end) as custom_ethnicity

, "Facts - Job Application Counts"."Number of Candidates" as n_candidate

#, "Job Application - Legislative Information"."Regulatory Response Ethnicity" as Ethnicity_Name

#, "Job Application - Legislative Information"."Regulatory Response Ethnicity Code" as Ethnicity_Code

from "Recruiting - Recruiting Real Time"

where

("Job Application - Legislative Information"."Regulatory Response Ethnicity Code" IS NOT NULL)

order by 1 desc nulls last, 2 desc nulls last, 3 asc nulls last, 4 asc nulls last, 5 asc nulls last

fetch first 7 rows only

Ameya_J

Thanks! This is really helpful.
Anyway i can utilise this functionality without needing Candidate Number field in the report? I have report with large volume of data (~1 million). So trying to sum the ethnicities by Job Requestion instead of pulling raw candidate numbers

Nathan CCC

Hi, Yes you can use this to aggregate (count, sum, average, min, max etc) any fact by any n dimensions. Nothing in this is specific to candidate number. Just replace candidate number with the one or more blue attribute dimension columns you want to group by. Yellow fact measure columns will always "roll up" across your dimensions like this.

Ameya_J

I tried but it did not work without having the candidate number. I am trying to break down the phases during a recruiting phase by candidate ethnicities. Here is a version of what my report looks like. Want to add ethnicity field (but the the 2 more ethnicity modification) to perform job application count for each 'ethnicity' at each 'Current State'

SELECT
"Job Requisition - Basic Information"."Requisition Number" saw_0,
"Department"."Department Name" saw_1,
"Job Requisition - Progression"."Current State" saw_2,
"Business Unit"."Business Unit Name" saw_3,
"Job Requisition - Dates"."Filled Date" saw_4,
COUNT(DISTINCT "Job Application - Basic Information"."Job Application ID") saw_5,
"Job Requisition - Additional Details"."Regular or Temporary" saw_6,
"Location"."Worker Location Name" saw_7,
"Grade"."Grade Name" saw_8,
"Job Application - Progression"."Current Phase" saw_9,
"Job Requisition - Hiring Team"."Recruiter Full Name" saw_10
FROM "Recruiting - Recruiting Real Time"