Agregações aninhadas

Sempre um nível de agregação em uma função

Um aplicativo típico pode conter:

• um milhão de registros nos dados
• cem linhas em uma tabela dinâmica
• um único KPI em um mostrador ou caixa de texto

Todos os três números ainda podem representar todos os dados, apesar da diferença de magnitude. Os números são apenas diferentes níveis de agregação.

Funções de agregação usam os valores de muitos registros como entrada e os reduzem a um único valor, que pode ser visto como um resumo de todos os registros. Há uma restrição: você não pode usar uma função de agregação dentro de outra. Em geral, é necessário que todas as referências de campo sejam agrupadas em exatamente uma função de agregação.

As seguintes expressões funcionarão:

• Sum(Sales)
• Sum(Sales)/Count(Order Number)

A seguinte expressão não funcionará, pois é uma agregação aninhada:

• Count(Sum(Sales))

A solução para isso vem na forma da função Aggr(). Ao contrário do seu nome, não se trata de uma função de agregação. Ela é uma função de "muitos para muitos", como uma matriz em matemática. Ela converte uma tabela com N registros em uma tabela com M registros. Ela retorna uma matriz de valores. Ela também pode ser considerada uma tabela estática virtual com uma única medida e uma ou várias dimensões.

Aggr() retorna um conjunto de valores da expressão, calculados sobre uma ou mais das dimensões indicadas. Por exemplo, o valor máximo de vendas por cliente, por região. Em agregações avançadas, a função Aggr() é incluída em outra função de agregação, usando a matriz de resultados da função Aggr() como entrada para a agregação na qual está aninhada.

Quando usada, a instrução Aggr() gera uma tabela virtual, com uma expressão agrupada por uma ou mais dimensões. O resultado dessa tabela virtual pode ser agregado ainda mais por uma função de agregação externa.

Calculando o maior valor médio de pedidos

Vamos usar uma instrução Aggr() simples em uma expressão de gráfico.

Queremos ver nossas métricas gerais no nível regional, mas também mostrar duas expressões mais complexas:

• Maior valor médio de pedidos por gerente em cada região.
• Gerente responsável pelo maior valor médio de pedidos.

Podemos calcular facilmente o valor médio de pedidos para cada região usando uma expressão padrão Sum(Sales)/Count([Order Number]).

Dentro do aplicativo, na pasta Nested Aggregations, você encontrará uma tabela com o título Aggr() function.

Faça o seguinte:

1. Selecione a tabela Aggr() function disponível.
   O painel de propriedades é aberto.
2. Clique em Adicionar coluna e selecione Medida.
3. Clique no símbolo .
   O editor de expressões é aberto.
4. Insira o seguinte: Sum(Sales)/Count([Order Number])
5. Clique em Aplicar.

Nosso objetivo é recuperar o maior valor médio de pedidos para cada região. Temos que usar Aggr() para dizer ao Qlik Sense que queremos obter o valor médio de pedidos para cada região, por gerente, e depois exibir o maior deles. Para obter o valor médio de pedido para cada região, por gerente, precisaremos incluir essas dimensões em nossa instrução Aggr():

Aggr(Sum(Sales)/Count([Order Number]), Region, Manager)

Essa expressão faz com que o Qlik Sense produza uma tabela virtual que se parece com esta:

Quando o Qlik Sense calcular os valores médios individuais dos pedidos para cada região, por gerente, precisaremos encontrar o maior deles. Fazemos isso envolvendo a função Aggr() com Max():

Max(Aggr(Sum(Sales)/Count([Order Number]), Manager, Region))

Faça o seguinte:

1. Clique em Adicionar coluna e selecione Medida.
2. Clique no símbolo .
   O editor de expressões é aberto.
3. Insira o seguinte: Max(Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
4. Clique em Aplicar.

Você pode ver o maior valor médio do pedido para todos os gerentes no nível da região. Esta é a primeira das nossas duas expressões complexas! O próximo requisito é exibir o nome do gerente responsável ao lado desses valores médios de pedidos grandes.

Para fazer isso, usaremos a mesma função Aggr() de antes, mas desta vez junto com a função FirstSortedValue(). A função FirstSortedValue() instrui o Qlik Sense a nos fornecer o gerente para a dimensão específica definida na segunda parte da função:

FirstSortedValue(Manager,-Aggr(Sum(Sales)/Count(Order Number), Manager, Region))

Faça o seguinte:

1. Clique em Adicionar coluna e selecione Medida.
2. Clique no símbolo .
   O editor de expressões é aberto.
3. Insira o seguinte: FirstSortedValue(Manager,-Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
4. Clique em Aplicar.