Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SUM AGGR

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 RefApplicant RefVacancy FTEVacancy StageVacancy State
ABCD100010.8InterviewScheduled
ABCD200010.8InterviewScheduled
ABCD300010.8InterviewScheduled
ABCD400010.8InterviewScheduled
BCDE500011.0InterviewScheduled
BCDE600011.0InterviewScheduled
BCDE700011.0InterviewScheduled
CDEF800012.0OutcomeSome Posts Filled
CDEF900012.0OutcomeSome Posts Filled
DEFG999991.4OutcomeAll Posts Filled
DEFG888881.4OutcomeAll Posts Filled
EFGH777770.5OutcomeAll Posts Filled
EFGH666660.5OutcomeAll Posts Filled
EFGH555550.5OutcomeAll Posts Filled
FGHI444445.0ShortlistingShortlising
FGHI333335.0ShortlistingShortlising
FGHI222225.0ShortlistingShortlising
GHIJ111111.0OfferOffer

I want to show The Following :

  

Expected Result
Vacancy FTEStageState
1.8InterviewScheduled
2.0OutcomeSome Posts Filled
1.9OutcomeAll Posts Filled
5.0ShortlistingShortlisting
1.0OfferOffer

I know it's simple, I just keep getting it wrong though.

Please help

Cheers

Phil

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Phil,

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

or simply

Sum(DISTINCT [Vacancy FTE])

Regards,

Antonio

View solution in original post

11 Replies
kamal_sanguri
Specialist
Specialist

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

Capture.PNG

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
kamal_sanguri
Specialist
Specialist

see attached

kamal_sanguri
Specialist
Specialist

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

Anonymous
Not applicable
Author

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

This is the correct one:

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

Capture.PNG

kamal_sanguri
Specialist
Specialist

refer this.. updated/correct one

antoniotiman
Master III
Master III

Hi Phil,

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

or simply

Sum(DISTINCT [Vacancy FTE])

Regards,

Antonio

Anonymous
Not applicable
Author

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