topic Re: SUM AGGR in QlikView

SUM AGGR

Anonymous — Fri, 16 Jun 2017 09:28:02 GMT

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

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 09:44:12 GMT

Use this as expression :

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

Re: SUM AGGR

Anil_Babu_Samineni — Fri, 16 Jun 2017 09:45:09 GMT

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

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 09:46:02 GMT

see attached

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 09:49:35 GMT

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

Re: SUM AGGR

Anonymous — Fri, 16 Jun 2017 09:53:37 GMT

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

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 09:54:15 GMT

This is the correct one:

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

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 09:56:20 GMT

refer this.. updated/correct one

Re: SUM AGGR

antoniotiman — Fri, 16 Jun 2017 09:58:42 GMT

Hi Phil,

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

or simply

Sum(DISTINCT [Vacancy FTE])

Regards,

Antonio

Re: SUM AGGR

Anonymous — Fri, 16 Jun 2017 10:27:27 GMT

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

Re: SUM AGGR

kamal_sanguri — Fri, 16 Jun 2017 10:36:15 GMT

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

Re: SUM AGGR

Anonymous — Fri, 16 Jun 2017 10:57:30 GMT

This works.

Thanks

Phil