Acumulado com Expressão

Fala pessoal! Tudo beleza?
 
Certamente vocês já fizeram algum relatório de Contabilidade e foi solicitado um indicador que possui os valores acumulados mês a mês, seja de Saldo, Lucro, etc.
 
Sabemos que a maioria dos gráficos possuem a função de acumular na aba de expressões, mas nem sempre esse acumulado vai atender a nossa demanda.
  
Gráficos que possuem a função de acumular: Barra, Linha, Combinado, Radar, Mekko, Dispersão, Grade e Table Simples.
 
Gráficos que não possuem a função de acumular: Mostrador, Tabela Dinâmica, Pizza, Funil e Blocos.
   
Imagine um cenário em que temos: um gráfico de tabela simples com o valor de REALIZADO e o valor de META. Precisamos agora criar mais três colunas: Realizado Acumulado, Meta Acumulada e % de Realizado em relação a meta.
 
Tranquilo! Gráfico de tabela simples possui a função de acumular a expressão por padrão.
  
Primeira expressão: Realizado Acumulado!
 
SUM(VALOR)
 
Marco Acumular, já era uma. 😀
  
Segunda expressão: Meta Acumulada!
 
SUM(META)
 
Marco Acumular, já era duas!
  
Terceira expressão: %!
 
Vou apenas reaproveitar as expressões…
  
[Realizado Acum.]/[Meta Acum.]
 
Pronto! Terminei!
   
Apenas verifico os valores só para ter certeza que não fiz nada errado
   
Jan/2015
Realizado Acum = 100
Meta Acum =  110
Percentual = 100/110 = 90,91%
Qlikview = 90,91% BOA!

   
Fev/2015
Realizado Acum = 220
Meta Acum =  240
Percentual = 220/240= 91,66%
Qlikview = 92,31% UÉ!
  
Bom, acho que preciso marcar o acumulado na expressão do percentual também!
 
Qlikview = 183,22%
 
NÃO É POSSÍVEL!
  
Deve ser algum erro de casa decimal (RSRSRS Eu sempre penso isso kkk!!!)
 
Refaço as contas e realmente não é erro de casa decimal.
 
Removo o acumulado das expressões: Realizado Acum e Meta Acum, e refaço o cálculo do percentual
  
Fev/2015
Realizado Acum = 120
Meta Acum =  130
Percentual = 120/130= 92,31%
  
Não acredito, o qlikview não consegue reutilizar a expressão com o acumulado. Afff! E agora?
  
Bom! Teremos então que investigar algumas funções que possam obter o mesmo resultado do acumulado do QlikView.
   

Funções

Hoje trabalharemos com as funções Funções Inter-Registro de Gráficos, são elas:
 
Above() = Acima – Retorna o valor da expressão avaliada com os valores de dimensão do gráfico da forma como aparecem na linha acima da atual em um segmento de coluna de uma tabela.
 
Below()Abaixo – Retorna o valor da expressão avaliado com os valores de dimensão do gráfico que aparecem na linha abaixo da atual dentro de um segmento de coluna em uma tabela.
 
Before() = Antes – Retorna o valor da expressão avaliada com os valores de dimensão de uma tabela dinâmica como aparecerem na coluna antes da atual.
 
After() = Depois – Retorna o valor da expressão avaliada com os valores de dimensão de uma tabela dinâmica como aparecerem na coluna depois da atual.
   

Características das funções

  • Na primeira coluna de um segmento de linha, um valor NULL será retornado, já que não há uma coluna
    antes dela.
  • Se a tabela for unidimensional ou se a expressão for precedida pelo qualificador total, o segmento de linha
    atual será sempre igual à linha inteira.
  • Especificar um deslocamento maior que 1 permite que você transfira a avaliação da expressão para essa outra coluna, por exemplo: Se usar para a função Before, então estará avaliando colunas a esquerda em relação a atual coluna.
  • Um número de deslocamento negativo tornará a função o seu inverso, por exemplo a função before ficará equivalente a função after.
  • A especificação de um deslocamento igual a 0 avaliará a expressão da coluna atual.
  • Ao especificar um terceiro parâmetro n maior que 1, a função retornará não um, mas uma série de valores n,
    um para cada n colunas da tabela. Dessa forma, a função pode ser usada como um argumento para qualquer uma das Funções de Intervalo de Gráfico especiais.

  
A parte acima grifada é muito importante, pois como queremos trabalhar com valores acumulados, então teremos que pegar o valor da linha/coluna atual e somar com o de linha(s)/coluna(s) anterior(es) e isso nos obriga a utilizar essas funções de intervalo de gráfico especiais.
  
Essas funções são precedidas da palavra Range, então temos: RangeSum, RangeAvg, RangeCount, RangeMin, RangeMax…. por aí vai.
  
IMPORTANTE: Essas funções de Range servirão para somar os resultados das funções Inter-Registros. Lembre-se que essas funções Inter-Registros retornaram vários resultados e é por isso que teremos que utilizar as funções de Range. Se tentássemos utilizar somente um SUM, o Qlikview retornaria nulo.
   

Exemplo

No exemplo em que vou comentar, estarei utilizando um gráfico de tabela simples com a dimensão MêsAno e suas respectivas expressões.
 
Se estamos trabalhando com um gráfico de tabela simples, então utilizaremos a função Inter-Registro Above(), pois o resultado do mês anterior esta na linha acima, desde que o gráfico esteja ordenado de forma Crescente.
 
Lembrando: Queremos acumular desde o primeiro MêsAno disponível nos filtros.
  
Expressão de Realizado: RangeSum(Above(SUM(VALOR), 0, RowNo()))
 
Vamos entender os parâmetros do Above
 
Deslocamento é zero, ou seja, estou avaliando a expressão da linha atual
 
N = RowNo() [RowNo() retorna o número da linha do gráfico]
 
Se estamos na primeira linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 1)), ou seja, estamos analisando somente a linha atual. Para a expressão RangeSum estamos somando o intervalo da linha atual até a linha atual.
 
Se estamos na segunda linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 2)), ou seja, estamos analisando a linha atual  e a linha anterior. Para a expressão RangeSum estamos somando o intervalo da linha atual até a linha anterior.
 
Se estamos na terceira linha do gráfico, então a expressão estará da seguinte forma: RangeSum(Above(SUM(VALOR), 0, 3)), ou seja, estamos analisando a linha atual  e duas linhas anteriores. Para a expressão RangeSum estamos somando o intervalo da linha atual até duas linhas anteriores.
  
Expressão de Meta: RangeSum(Above(SUM(META), 0, RowNo()))
 
A regra se mantém a mesma para a meta.
 
Agora o percentual fica fácil:
 
RangeSum(Above(SUM(VALOR), 0, RowNo())) / RangeSum(Above(SUM(META), 0, RowNo()))
 
Agora teremos o seguinte resultado
  
Grafico01
  
Muito bom!
   

Fique Atento

Lembre-se de escolher corretamente a expressão Inter-Registro a ser utilizada, se estiver em uma tabela dinâmica, com o MêsAno pivoteado, então temos que pegar o resultado da COLUNA ANTERIOR, para isso devemos utilizar a função Before().
 
Abaixo disponibilizei uma aplicação com um exemplo na tabela dinâmica e como acumular somente alguns passos, por exemplo: Somente 1 mês anterior ou somente dois meses anteriores.
 
Para fazer download da aplicação de exemplo clique aqui!
   

Conclusão

Se o que você deseja fazer o QlikView oferece, então utilize! Sempre as funções prontas do QlikView oferecem melhor performance, mas nem tudo o QlikView nos oferece de “mão beijada”, desta forma é bom estarmos preparados para essas ocasiões.
 
Até a próxima semana!

Anúncios

15 pensamentos sobre “Acumulado com Expressão

  1. Muito bom!
    Parabéns pelo blog e pelos conteúdos, explicados de maneira objetiva e fácil!

    Quanto ao post de hoje ele é essencial, sempre fiz expressões para acumular mais nunca assim!

    Vou aproveitar e muito!

  2. Yuri,

    Vale ressaltar que no seu exemplo as colunas que contém a função above/before não mantém o acumulado “fixo” ao fazer filtros, exemplo:

    Sem realizar filtro na tabela:

    Valores acumulados sem o filtro (valor de fev/2015:

    Realizado: 120
    Acum Mês Anterior: 220
    Acum 2 Mêses Ant…: 220
    Acum Total: 220

    Ao filtrar fev/2015:

    Realizado: 120
    Acum Mês Anterior: 120
    Acum 2 Mêses Ant…: 120
    Acum Total: 120

    • Sim, porque estamos acumulando via expressão no gráfico. O ponto de partida do nosso acumulado é o primeiro valor exibido na dimensão do gráfico. Para que esse acumulado seja fixo, devemos faze-lo no script da aplicação

      • Sim, exatamente!

        A “vantagem” de se fazer diretamente no gráfico é que se precisar o acumulado por mês, ano, trimestre e etc, basta adicionar os campos “datas” em dimensões que a expressão irá fazer o acumulado. Já no script teria que tratar o acumulado para mês,ano e etc.

        Acabou publicando o mesmo comentário, se possível excluir.

      • Sabe dizer se tem alguma solução para tratar acumulado para mês,ano, dia e etc no script?

        A função Peek/Previous com o Group By resolveria, porém, teoricamente teria que repetir para cada campo data.

      • Paulo, você terá que fazer com Peek mesmo e sim, terá que repetir a operação para cada “tipo” de acumulado, por dia, mês e por ano.
        Esse é um post que estou para colocar aqui no blog

  3. Yuri,

    Vale ressaltar que no seu exemplo as colunas que contém a função above/before não mantém o acumulado “fixo” ao fazer filtros, exemplo:

    Sem realizar filtro na tabela:

    Valores acumulados sem o filtro:

    Realizado: 120
    Acum Mês Anterior: 220
    Acum 2 Mêses Ant…: 220
    Acum Total: 220

    Ao filtrar fev/2015:

    Realizado: 120
    Acum Mês Anterior: 120
    Acum 2 Mêses Ant…: 120
    Acum Total: 120

    • Boa noite, estou precisando exatamente disso. Que ao filtrar um mês este passe a ser o último registro a ser mostrado na tabela, mas que seja mantido os demais anteriores… devendo, também, ser mantido o valor acumulado.

      Alguma ideia?

      Grato pela atenção e pelo artigo.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s