Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I just can't get my head around this AGRR()function, and I really need it.
I want to Aggr a total based on the sum of the jobref. Please see my data below:
Job Ref | Applicant Ref | Vacancy FTE | Vacancy Stage | Vacancy State |
ABCD | 10001 | 0.8 | Interview | Scheduled |
ABCD | 20001 | 0.8 | Interview | Scheduled |
ABCD | 30001 | 0.8 | Interview | Scheduled |
ABCD | 40001 | 0.8 | Interview | Scheduled |
BCDE | 50001 | 1.0 | Interview | Scheduled |
BCDE | 60001 | 1.0 | Interview | Scheduled |
BCDE | 70001 | 1.0 | Interview | Scheduled |
CDEF | 80001 | 2.0 | Outcome | Some Posts Filled |
CDEF | 90001 | 2.0 | Outcome | Some Posts Filled |
DEFG | 99999 | 1.4 | Outcome | All Posts Filled |
DEFG | 88888 | 1.4 | Outcome | All Posts Filled |
EFGH | 77777 | 0.5 | Outcome | All Posts Filled |
EFGH | 66666 | 0.5 | Outcome | All Posts Filled |
EFGH | 55555 | 0.5 | Outcome | All Posts Filled |
FGHI | 44444 | 5.0 | Shortlisting | Shortlising |
FGHI | 33333 | 5.0 | Shortlisting | Shortlising |
FGHI | 22222 | 5.0 | Shortlisting | Shortlising |
GHIJ | 11111 | 1.0 | Offer | Offer |
I want to show The Following :
Expected Result | ||
Vacancy FTE | Stage | State |
1.8 | Interview | Scheduled |
2.0 | Outcome | Some Posts Filled |
1.9 | Outcome | All Posts Filled |
5.0 | Shortlisting | Shortlisting |
1.0 | Offer | Offer |
I know it's simple, I just keep getting it wrong though.
Please help
Cheers
Phil
Try this..
Aggr(Sum(DISTINCT [Vacancy FTE]), [Job Ref], [Vacancy FTE],[Vacancy State],[Vacancy Stage]).
Also add Job Ref as dimension. See, if that works for you
This works.
Thanks
Phil