Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bruno_m_santos
Partner - Creator
Partner - Creator

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

1 Reply
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.


talk is cheap, supply exceeds demand