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

    Aggregate, max and sum

    Vítor Castro

      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.

       

      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
          Stefan Wühl

          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
              Vítor Castro

              It works

               

              thank you very much by your help

              • Re: Aggregate, max and sum
                Vítor Castro

                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
                    Stefan Wühl

                    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
                    Shantanu Gupta

                    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
                    Sunny Talwar

                    Try this:

                     

                    =FirstSortedValue(DISTINCT id_person,-Aggr(

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