Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello!
I have a data table
My goal is to calculate a metric of average time of job position being filled.
I try to use a following expression but I have a problem with it:
avg(
aggr(
If(IsNull(max({<StageName={"JO Accepted"}>} ActivityDate)), max({<StageName={"Hired"}>} ActivityDate),
max({<StageName={"JO Accepted"}>} ActivityDate))
- JobStartDate
,@CandidateId)
)
Result is not displayed for some jobs until I deliberately select those jobs.
For some of them I can see a result only if selected one by one
I tried to play with set expression inside/outside aggr but I didn't get satisfying result.
Thanks for any help, but please don't advise to change data model. I want to understand how to use set expression inside AGGR().
I attach my data file.
Thank you for your reply! You are right. I already got help. The first point I mised to get correct result was grouping by @CandidateId and @JobId, and second point was using of Nodistinct.
avg(
aggr( NODISTINCT
If(IsNull(max({<StageName={"JO Accepted"}>} ActivityDate)), max({<StageName={"Hired"}>} ActivityDate),
max({<StageName={"JO Accepted"}>} ActivityDate))
- JobStartDate
,@JobId,@CandidateId)
)
Hi,
my guess is that you problem is that you aggregate by Candidate but the dimension of your Table is JobId have you tried to aggregate by Candidate AND JobId?
aggr(expression(),@Candidate,@JobID)
Thank you for your reply! You are right. I already got help. The first point I mised to get correct result was grouping by @CandidateId and @JobId, and second point was using of Nodistinct.
avg(
aggr( NODISTINCT
If(IsNull(max({<StageName={"JO Accepted"}>} ActivityDate)), max({<StageName={"Hired"}>} ActivityDate),
max({<StageName={"JO Accepted"}>} ActivityDate))
- JobStartDate
,@JobId,@CandidateId)
)