Not all data displayed for SET experrion inside AGGR

[Andrei_Faibich]

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.

[Oliver_F]

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)

[Andrei_Faibich · Accepted solution]

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)
      )