Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrei_Faibich
Contributor III
Contributor III

Not all data displayed for SET experrion inside AGGR

Hello!

I have a data table

Andrei_Faibich_0-1671823127882.png

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.

Andrei_Faibich_1-1671823769599.png

For some of them I can see a result only if selected one by one

Andrei_Faibich_2-1671823977146.png

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.

Labels (2)
1 Solution

Accepted Solutions
Andrei_Faibich
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
Oliver_F
Partner - Creator III
Partner - Creator III

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
Contributor III
Contributor III
Author

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