1 Reply Latest reply: Mar 27, 2013 3:05 PM by Gysbert Wassenaar RSS

    Set analysis why need aggr function

    Bruno Santos

      Hi guys,

       

      I'm having the following problem:

      I created a setanalysis, which sum all data on the conditions shown below. With the  records found I pretend calculate [Quantity] *[ValorMoeda] *[Unit Value].

      However when I put the expression "set analysis" without the aggregation condition the sum of the lines was incorrect. After some research, I came to the following expression, but I had serious doubts:

       

      Why I have to do an aggregation inside a Set Analysis?

      Form me it is the first time that the sum does not equal the lines!

       

      Despite apparently having reached a solution, I need understand the impacts. If anyone can help..

       

      Thanks in advance.

      Bruno

       

       

       

       

      =sum({$<MonthID = ,
      Link_Date = {"<=$(=Date(MakeDate(Max(Year) , Max([Month (#)]), Day(Max(Link_Date))),'DD-MM-YYYY'))"},
      Year = ,
      Quarter = ,
      Period = ,
      [Period (#)] = ,
      Month = ,
      [Recurso Humano  ]={"=Len([Recurso Humano  ]) = 0"},
      [Número de Encomenda NAV  ]={"=Len([Número de Encomenda NAV  ]) = 0"},
      [Aprovação de Requisição  ]={"Approved"},
      [Reembolso  ]={"Não"},
      Quant_Pend={">0"},
      [Quantidade Satisfeita  ]={"0"}
      >}
      aggr(
      sum({<MonthID = ,
      Link_Date = {"<=$(=Date(MakeDate(Max(Year) , Max([Month (#)]), Day(Max(Link_Date))),'DD-MM-YYYY'))"},
      Year = ,
      Quarter = ,
      Period = ,
      [Period (#)] = ,
      Month = ,
      [Recurso Humano  ]={"=Len([Recurso Humano  ]) = 0"},
      [Número de Encomenda NAV  ]={"=Len([Número de Encomenda NAV  ]) = 0"},
      [Aprovação de Requisição  ]={"Approved"},
      [Reembolso  ]={"Não"},
      Quant_Pend={">0"},
      [Quantidade Satisfeita  ]={"0"}>} [Quantidade  ]*[ValorMoeda  ]*[Valor Unitário  ]),[Document No],Date)) 
      
      
        • Re: Set analysis why need aggr function
          Gysbert Wassenaar

          You calculate a sum of sums. I don't know why, but that's what you're doing. Are you perhaps using this expression a pivot table? Anyway, if you nest aggregation functions then you need an aggr to create an intermediate virtual table with the lower level sums. These are then summed by the outer sum. Possibly you don't need the set modifiers in the outer sum.