8 Replies Latest reply: Jun 15, 2016 9:33 AM by Stefan Wühl

Aggregate, max and sum

Hello everyone

I am having some problems in an set analysis operation.

I have this table

ID-PersonWorkGoal
576212224
605215217
58738383854
5491111
572271271

i want to find the max (%) of work/goal of all persons but including the max work. For this table i want the max(work/goal) and the result can be:

id_person 549 or 572. But as i want too the max(work) the result should be 572.

=FirstSortedValue(Distinct id_person,-aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count),id_person)) and the result is 549 (the first possibility that appears) and i want 572 because the field "work" is bigger.

Can someone help me?

Greetings

Vítor

• Re: Aggregate, max and sum

Try something like

=FirstSortedValue(Distinct

id_person,

-aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count)+sum({\$<type={'work'}>}#Count)/pow(10,9),

id_person)

)

i.e. add a small number proportional to work load to break the ties.

• Re: Aggregate, max and sum

It works

thank you very much by your help

• Re: Aggregate, max and sum

why did you sum?

why the pow?

if we need the opposite effect, do we just need to do something like (minimum goal/work with minimum work):

=FirstSortedValue(Distinct

id_person,

aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count)-sum({\$<type={'work'}>}#Count)/pow(10,9),

id_person)

)

just to learn a little bit

thank you very much

• Re: Aggregate, max and sum

The syntax for FirstSortedValue() is

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

You want to create a list of persons, and your sort weight should be Work/Goal per person, hence your original expression:

=FirstSortedValue(Distinct

id_person,

-aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count),id_person)

)

(To create the sort weight per person, use advanced aggregation with dimension id_person and the aggregated Work divided by aggregated goal as expression. You only need to sum work / goal if there might be several records per person id for work and goal. Essentially, this is your original expression.)

The issue with FirstSortedValue() is that it returns NULL if there are two or more lines in the sort weight temporary table with the same highest sort order. You can use DISTINCT to overcome this and return the first line's value in LOAD order.

But your requirement is different: If there are ties, consider the one with the higher work amount.

So the rule is: sort by work / goal, get the lines with the highest quotient and of there are more than one, the one with the highest work amount.

(Note that I am talking about highest values here, which we achieve by using an additional minus before the aggr(), because FirstSortedValue() basically looks for the lowest value)

You can do this by adding a small number to the original sort weight,proportional to work amount, but this number needs to be much smaller than the typical sort weight value, it just should play a role when there are equal original sort weight values, to break the tie.

Note also that it doesn't make a big difference if you adding the work divided by a large number, to create the small tie-breaker proportional to work load (my version) or if you multiplying the original sort weight by a large number, then just add the work load (Sunny's version).

It's only important that the factor is large enough to not change the original sort order per person_id, it should only break ties.

My number was large enough, Sunny's number wasn't for your data. But the factor you need to chose may depend on your data.

It then doesn't matter if you write pow(10,9) or 1000000000.

if you want to find minimum work/goal with minimum work, you need to first remove the minus before the aggr() function

=FirstSortedValue(Distinct

id_person,

aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count),

id_person)

)

Then to break ties,you need to create a smaller value with lower work, so I think you should use:

=FirstSortedValue(Distinct

id_person,

aggr(sum({\$<type={'work'}>}#Count)/sum({\$<objective={'goal'}>}#Count) + sum({\$<type={'work'}>}#Count)/pow(10,9),

id_person)

)

Regards,

Stefan

• Re: Aggregate, max and sum

Hi swuhel,

Very shrewd approach , just wanted to check the reason of using pow(10,9) as pow(10,4) and onwards should serve the purpose.

• Re: Aggregate, max and sum

Try this:

=FirstSortedValue(DISTINCT id_person,-Aggr(

(Sum({\$<type={'work'}>}#Count)/Sum({\$<objective={'goal'}>}#Count) * 100000) + Sum({\$<type={'work'}>}#Count) ,id_person))

• Re: Aggregate, max and sum

Your expression gives me the max (work) --> 587. Not the max (work) which % = 100

• Re: Aggregate, max and sum

If my response is not the correct response, please mark Stefan's response as the correct answer.

Best,

Sunny