topic Re: Aggregate, max and sum in QlikView

Aggregate, max and sum

vdcastro — Thu, 09 Jun 2016 13:35:25 GMT

Hello everyone

I am having some problems in an set analysis operation.

I have this table

ID-Person	Work	Goal
576	212	224
605	215	217
587	3838	3854
549	11	11
572	271	271

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.

I made this expression:

=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

swuehl — Thu, 09 Jun 2016 13:44:51 GMT

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

sunny_talwar — Thu, 09 Jun 2016 13:48:23 GMT

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

vdcastro — Thu, 09 Jun 2016 15:06:05 GMT

Thank you by your answer.

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

Re: Aggregate, max and sum

vdcastro — Thu, 09 Jun 2016 15:08:18 GMT

It works

thank you very much by your help

Re: Aggregate, max and sum

sunny_talwar — Thu, 09 Jun 2016 15:17:44 GMT

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

Best,

Sunny

Re: Aggregate, max and sum

vdcastro — Wed, 15 Jun 2016 10:37:51 GMT

I would like to understand better your answer...

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

Wed, 15 Jun 2016 12:19:30 GMT

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

swuehl — Wed, 15 Jun 2016 13:33:35 GMT

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