0 Replies Latest reply: Jun 8, 2010 4:37 AM by GuyShifrin RSS

    Nested aggregation

    GuyShifrin

       

      Hi,

      I need to nest aggregation functions for my project demands. I need to nest concat inside sum funcion .

      I use aggr function to enable the nesting of aggregation functions.

      Here is my code. It is one of expressions in pivot table, where Agent.FullName is a dimension.

      =sum(if(ComplexTargets.Areas = aggr(concat( distinct AreasForAgentByDate.AreaCode,'*'),Agent.FullName),ComplexTargets.Target))

      concat( distinct AreasForAgentByDate.AreaCode,'*') - returns me the right value

       



      The equality : ComplexTargets.Areas = aggr(concat( distinct AreasForAgentByDate.AreaCode,'*'),Agent.FullName) is works when it is not in outside aggregation function and i checked that there is matching values.

      AreasForAgentByDate.AreaCode is connected to the data cloud, whereas ComplexTargets.Areas (ComplexTargets table) is not connected - stand alone table.

      I get 0 for all agents, like there is no matching values for anybody.

      Do you have any idea what can be the problem ?

      Thanks a lot.

      Guy