Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
philmywallet
Contributor

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

Re: SUM AGGR

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
Valued Contributor

Re: SUM AGGR

Use this as expression :

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

Re: SUM AGGR

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
kamal_sanguri
Valued Contributor

Re: SUM AGGR

see attached

Highlighted
kamal_sanguri
Valued Contributor

Re: SUM AGGR

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

philmywallet
Contributor

Re: SUM AGGR

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
Valued Contributor

Re: SUM AGGR

This is the correct one:

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

Capture.PNG

kamal_sanguri
Valued Contributor

Re: SUM AGGR

refer this.. updated/correct one

antoniotiman
Honored Contributor III

Re: SUM AGGR

Hi Phil,

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

or simply

Sum(DISTINCT [Vacancy FTE])

Regards,

Antonio

View solution in original post

philmywallet
Contributor

Re: SUM AGGR

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