Set analysis why need aggr function

bruno_m_santos — Wed, 27 Mar 2013 18:41:55 GMT

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 — Wed, 27 Mar 2013 19:03:03 GMT

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.

topic Set analysis why need aggr function in QlikView

Set analysis why need aggr function

Re: Set analysis why need aggr function