Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bruno_m_santos
Contributor

Set analysis why need aggr function

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))

Tags (2)
1 Reply

Re: Set analysis why need aggr function

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.


talk is cheap, supply exceeds demand
Community Browser