SUM AGGR

[Anonymous]

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

[kamal_sanguri]

Use this as expression :

Aggr(Avg([Vacancy FTE]),[Vacancy Stage],[Vacancy State])

[Anil_Babu_Samineni]

May be this for

Stage

FirstSortedValue([Vacancy Stage], Aggr(Sum([Vacancy FTE]), [Vacancy Stage]))

Stage

FirstSortedValue([Vacancy State], Aggr(Sum([Vacancy FTE]), [Vacancy State]))

I got like below from given data

[kamal_sanguri]

see attached

[kamal_sanguri]

or may be you want to change AVG to Max or Sum.

[Anonymous]

Nearly .... but not quite

All the solutions do not total the correct jobref FTE. 

E.g.  Outome all posts filled should show 1.9 FTE

[kamal_sanguri]

This is the correct one:

Aggr(Sum(DISTINCT [Vacancy FTE]),[Vacancy State],[Vacancy Stage])

[kamal_sanguri]

refer this.. updated/correct one

[antoniotiman · Accepted solution]

Hi Phil,

Sum(Aggr(Sum(DISTINCT [Vacancy FTE]),[Job Ref]))

or simply

Sum(DISTINCT [Vacancy FTE])

Regards,

Antonio

[Anonymous]

So close, but not quite there ...

This calculates a distinct count based on the value of the stage or state so if there are 10 vacancies with 1 FTE on each, this expression only sums 1 FTE (not 10).

I tried to add the Job Ref in the script but it came back blank.

Aggr(Sum(DISTINCT [Vacancy FTE]),[Vacancy State],[Vacancy Stage],[Job Ref])

Any other thoughts?

Phil