Acumulado

É comum em QlikView (ou Sense) termos uma solicitação de implementar uma tabela (ou gráfico) contendo o acumulado do ano em uma coluna


Existem várias técnicas para fazer isso, algumas utilizam fórmulas relativamente complexas, valendo-se de funções tais como “RangeSum”, “Above”, “RowNo”, etc. Mesmo assim, sofrem do efeito colateral de que se o usuário fizer um filtro, pode ser que o valor seja comprometido.

Vou ensinar aqui a técnica conhecida como “AsOf”, que consiste em gerar um calendário, com “flags” apropriados que irão simplicificar sua análise.

Um exemplo,

Vendas:
LOAD
    Date(Today() - RecNo()) as Data, 
    RecNo() & IterNo()        as NumeroNF,    
    num(round(Rand() * 1000,0.01),'R$ #.##0,00;-R$ #.##0,00') as ValorVenda
AutoGenerate 400
While IterNo() < 5;

Calendario: // Tabela temporaria com todas as datas
LOAD 
    FieldValue('Data',RecNo()) as Data
AUTOGENERATE FieldValueCount('Data');

CalendarioAsOf:        // Periodo Atual
LOAD Distinct
     Data         as Data     
     ,Data         as AsOfData
    ,'Atual'     as AsOfTipo
RESIDENT Calendario;

tmp: LOAD Min(Data) as min, Max(Data) as max Resident Calendario; // Limitador Registros
LET LimiteInferior = num(Peek('min'));
LET LimiteSuperior = num(Peek('max'));
Drop Table tmp;    

Concatenate(CalendarioAsOf) // Acumulado Geral
LOAD Distinct
      num(Data - IterNo() +1)     as Data     
    ,Data                        as AsOfData
    ,'Acumulado Geral'             as AsOfTipo
RESIDENT Calendario
WHILE num(Data - IterNo() +1) >= $(LimiteInferior);

Concatenate(CalendarioAsOf) // Acumulado Anual
LOAD * WHERE Year(Data) = Year(AsOfData);
LOAD Distinct
      num(Data - IterNo() +1)     as Data     
    ,Data                        as AsOfData
    ,'Acumulado Anual'             as AsOfTipo
RESIDENT Calendario
WHILE num(Data - IterNo() +1) >= $(LimiteInferior);

CONCATENATE (CalendarioAsOf) //3 meses
LOAD * WHERE MonthStart(Data) >= MonthStart(AddMonths(AsOfData,-2)) and MonthStart(Data) <= MonthStart(AsOfData); 
LOAD Distinct
      num(Data - IterNo() +1)     as Data     
    ,Data                        as AsOfData
    ,'3 Meses'                     as AsOfTipo
RESIDENT Calendario
WHILE num(Data - IterNo() +1) >= $(LimiteInferior);

CONCATENATE (CalendarioAsOf) //Mes Anterior
LOAD * WHERE MonthStart(Data) >= MonthStart(AddMonths(AsOfData,-1)) and MonthStart(Data) < MonthStart(AsOfData); 
LOAD Distinct
      num(Data - IterNo() +1)     as Data     
    ,Data                        as AsOfData
    ,'Mes Anterior'             as AsOfTipo
RESIDENT Calendario
WHILE num(Data - IterNo() +1) >= $(LimiteInferior);

inner join(CalendarioAsOf)
load 
     Data,
     Month(Data) as Mes,
     Year(Data) as Ano,
     AsOfData,
     Date(MonthStart(AsOfData),'MMM/YYYY') as AsOfMes,
     Year(AsOfData) as AsOfAno,
     AsOfTipo
Resident CalendarioAsOf;
drop Table Calendario;

 


Com esse código, teremos uma tabela “CalendarioAsOf” que irá conter todas as datas que logicamente compõe o “Acumulado até agora”, ou seja todas as datas até aquela data em específicoAsOf Data

Construindo-se uma tabela como essa simplifica enormemente a construção de tabelas acumuladas, onde podemos incluir nessa tabela “CalendarioAsOf” todos as condições que precisarmos: YTD, MTD, LYTD, MesAtual, MesAnterior etc

 

 

2 respostas para “Acumulado”

  1. Caro Clever,

    Entendi que essa solução faz uma acumulação partindo de um intervalo de datas que consta na sua base (21/09/14 a 26/09/14).

    Tenho um problema sensivelmente diferente:
    Tenho extrações diárias do sistema transacional a partir do 2º semestre de um ano, mas precisaria calcular o estoque para o primeiro semestre também.

    Os dados estão dispostos assim:
    Processo | Data de Ingresso | Data de Julgamento | Data de Devolução
    100 | 01-02-2016 | 01-05-2016 | 01-07-2016
    101 | 02-02-2016 | 01-06-2016 | NULL
    102 | 02-02-2016 | 01-05-2016 | 01-07-2016
    103 | 01-03-2016 | NULL | NULL

    Dessa forma, o estoque de processos não julgados no último dia de cada mês seria assim:
    fev/16 — 03
    mar/16 — 04
    abr/16 — 04
    mai/16 — 02
    jun/16 — 01
    jul/16 — 01

    Alguma ideia de como fazer isso?

    1. Oi, Sergio, tudo bem?
      Sugiro criar um tópico na comunidade (me passe o link depois)
      Descreva o máximo de informações que puder, se possível anexe o que já fez até o momento e uma amostra dos seus dados

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *