10 Replies Latest reply: Aug 25, 2017 3:01 PM by Stefan Wühl RSS

    How does AGGR function exactly work in case of missing values?

    Vlastimil Kosik

      Hi Guys,

       

      Perhaps you could help me here. I struggle to understand how exactly AGGR function works when I try to involve two aggr functions in one calculation.

       

      Lets say I have the following formula:

       

      aggr ( {< [Client TAG] = {'No'} >} sum( EBIT ) , ID , Year)

      /

      aggr ({ <[Client TAG] = {'No'} >} sum( Turnover ), ID , Year )


      If there are no missing values this seems to work just fine and it gives a simple result (RESULT = outcome of the given formula)


      aggr(EBIT)                aggr(TURNOVER)       RESULT

      1                                 1                                  1

      2                                 2                                  1       

      2                                 1                                  2

      4                                 1                                  4

       


      However, if there would be a missing value lets say in second row in Turnover, how this would look like in the AGGR temp tables calculation?


      aggr(EBIT)                aggr(TURNOVER)       RESULT

      1                                 1                                  1

      2                                 1                                  2       

      2                                 1                                  2

      4                                 -                                   -


      OR


      aggr(EBIT)                aggr(TURNOVER)       RESULT

      1                                 1                                  1

      2                                 -                                   -       

      2                                 1                                  2

      4                                 1                                  4