Dificuldade em usar a função Previous()

[msludo]

Bom dia,

Estou tentando resolver o seguinte problema:

Sou nova no Qlik e estou com Uma planilha em excel de Dados orçamentários de Receita e anulação de Receita e precisava obter Como resultado uma Lista de Receitas com o valor das anulações sendo abatido do valor da Receita, se for o Caso. Ex.:

Evento Contabil (Nome)	(Documento numero)	Valor Documento
RECEITA	aaaa.bbbb.ccc.ddddd.eee	1200,00
ANULAÇÃO DA RECEITA	aaaa.bbbb.ccc.ddddd.eee.001	1000,00
ANULAÇÃO DA RECEITA	aaaa.bbbb.ccc.ddddd.eee.002	200,00

Essas linhas deveriam resultar em:

Evento Contabil (Nome)	Movimento Contabil (Documento)	Valor Documento
RECEITA	aaaa.bbbb.ccc.ddddd.eee	0

Escrevi o seguinte código:

[Relatório 1]:
LOAD
[Evento Contabil (Código)],
[Evento Contabil (Nome)],
[Período Movimento (Data Completa)],
[Movimento Contabil (Documento)],
MID([Movimento Contabil (Documento)],1,23) AS CODIGO,
IF([Evento Contabil (Código)] ='66034A', //tornando o valor das anulações negativas
[Valor Documento]*(-1),
[Valor Documento]
) as [Valor Documento]
FROM [lib://Downloads/TESTE.xlsx]
(ooxml, embedded labels, table is [Relatório 1]);

//Criada uma tabela temporária, cópia da tabela Relatório1, para fazer a diferença entre RECEITA DE APROPRIAÇÃO DE DESPESA
//e ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA
//Para isso foi utilizada a função Previous().
//Funciona da seguinte maneira:
//1.É realizada a comparação do valor de CODIGO da linha corrente com a linha anterior (previous). Se forem idênticas,
//significa que se trata de uma RECEITA com sua respectiva ANULAÇÃO. Então, o passo 2 é executado. Se não forem idênticas, o passo 3 é executado.
//2.O VALOR_DOCUMENTO da linha anterior, que é o valor de ANULAÇÃO, é subtraída do VALOR_DOCUMENTO da linha corrente, que é o de RECEITA.
//Esse resultado é armazenado na linha corrente.
//3.Caso os códigos não forem idênticos, é mantido o VALOR_DOCUMENTO original
//o campo VALOR_DOCUMENTO é o resultado final esperado.

TEMP:
LOAD *,
IF(CODIGO = Previous(CODIGO), //1.
([Valor Documento] + Previous([Valor Documento])), //2.
[Valor Documento]) AS VALOR_DOCUMENTO //3.
RESIDENT [Relatório 1]
ORDER BY [Movimento Contabil (Documento)] desc;

//Como Relatório 1 não será mais utilizado (e também para não gerar chaves sintéticas), essa tabela é excluída.
DROP TABLE [Relatório 1];

//Criada a tabela [Receita Apropriação Despesa], que é uma cópia da tabela TEMP, mantendo somente as linhas que contém RECEITA.

NoConcatenate
[Receita Apropriação Despesa]:
LOAD
[Evento Contabil (Código)],
[Evento Contabil (Nome)],
[Movimento Contabil (Documento)] as [Número Documento OP],
VALOR_DOCUMENTO as [Valor Documento]
RESIDENT TEMP
WHERE [Evento Contabil (Código)] LIKE '66040N';

//Como a tabela TEMP será mais utilizado (e também para não gerar chaves sintéticas), essa tabela é excluída.
DROP TABLE TEMP;

O problema que enfrento é: se há somente uma anulação para uma receita, a conta dá certo, porém se houver mais de uma anulação, a conta não dá certo. Alguem pode me ajudar?

Segue o arquivo excel.

[marcelo_borges]

Olá primeiramente bem-vinda ao mundo do Qlik. Sou iniciante também, mas creio que encontrei a solução para o seu problema usando uma outra forma, vou explicar o raciocínio.

A dificuldade que observei, foi que a ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA possui o código da RECEITA DE APROPRIAÇÃO DE DESPESA acrescida de 2 dígitos. Para resolver isso criei 2 colunas, uma somente o código da RECEITA DE APROPRIAÇÃO DE DESPESA e outro para os dígitos caso seja ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA.

mid([Movimento Contabil (Documento)],1,23) as movCont,
    if(len([Movimento Contabil (Documento)])>23,mid([Movimento Contabil (Documento)],25,26))as DigMovCont,

Por ultimo, transformei os valores da ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA em negativo:

if([Evento Contabil (Nome)]='ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA',[Valor Documento]*-1,[Valor Documento]) as ValorDocumento

Com isso, eu posso fazer uma soma simples do campo ValorDocumento, utilizando como dimensões [Evento Contabil (Código)] e [Período Movimento (Data Completa)].

Código Completo

[Relatório 1]:
LOAD
	[Evento Contabil (Código)],
	[Evento Contabil (Nome)],
	[Período Movimento (Data Completa)],
	[Movimento Contabil (Documento)],
    mid([Movimento Contabil (Documento)],1,23) as movCont,
    if(len([Movimento Contabil (Documento)])>23,mid([Movimento Contabil (Documento)],25,26))as DigMovCont,
    //mid([Movimento Contabil (Documento)],20,23) as DigMovCont,
    
	if([Evento Contabil (Nome)]='ANULAÇÃO DA RECEITA DE APROPRIAÇÃO DE DESPESA',[Valor Documento]*-1,[Valor Documento]) as ValorDocumento
 FROM [lib://AttachedFiles/TESTE.xlsx]
(ooxml, embedded labels, table is [Relatório 1]);

Tabela

[msludo]

Olá, Marcelo!

Então, havia feito algo semelhante ao que você me sugere, porém preciso ter como saída um excel com as receitas somente. O valor Total deveria aparecer na linha da receita, substituindo o valor original da receita, entende?

Não sei se chegou a testar o que fiz, mas funcionou quando há uma anulação para uma receita, sse houver duas anulações, ja não funciona, e sinceramente não consigo nem interpretar o cálculo final que o script faz para quando há duas anulações, é como se a função Previous() ficasse bugada. 🙂

[marcelo_borges]

Funcionou com as duas anulações sem nenhum problema, no caso da tabela , é só tirar as dimensões que agrupam os resultados. olha o print abaixo.