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