Re: Ajuda com Tabela de Projeção de Dados - Qlik Community

kkmoraes · ‎2016-05-04

Prezados,

Boa tarde!

Gostaria da ajuda de vocês para tentar solucionar o problema abaixo, a descrição é um pouco longa pois tentei apresentar os cenários da forma mais transparente possível.

Estou construindo um BI que irá realizar Previsões de Dívidas Baseado em alguns campos de valores.

Irei mostrar os cenários abaixo:

Projeção da Dívida Baseado no Valor da Assinatura do Contrato.

Essa projeção eu consegui realizar da seguinte forma:

Carrego os campos referentes ao contrato em um QVD (resumi o LOAD no Script abaixo para facilitar o entendimento)

GDP_CONTRATO:
LOAD
    GDPCONTR_SQ                                                                        AS [Código Sequencial do Contrato],
    GDPCONTR_NU                                                                        AS [Numero do Contrato],
    GDPCONTR_NU & '|' & GDPCONTR_SQ                                                    AS ChaveContrato,
    Num(GDPCONTR_VL_CONTRATO,'#.##0,00;-R$ #.##0,00')                                  AS [Valor Total do Contrato],
    Num(GDPCONTR_VL_CONTRAPARTIDA,'#.##0,00;-R$ #.##0,00')                            AS [Valor da Contrapartida do Contrato],
    Num(GDPCONTR_VL_DEDUCAO,'#.##0,00;-R$ #.##0,00')                                  AS [Valor Deduzido da Contrapartida do Contrato],
    DATE(GDPCONTR_DT_ASSINATURA)                                                      AS [Data Assinatura do Contrato],
    DATE(GDPCONTR_DT_INICIO_CARENCIA)                                                  AS [Data Inicio da Carência],
    DATE(GDPCONTR_DT_FIM_CARENCIA)                                                    AS [Data Fim da Carência],
    Floor((Interval(GDPCONTR_DT_FIM_CARENCIA-GDPCONTR_DT_INICIO_CARENCIA,'d')/30))    AS [Carência],
    DATE(GDPCONTR_DT_INIC_AMORTIZACAO)                                                AS [Data Inicio Amortização],
    GDPCONTR_QT_PARCELA_AMORT                                                          AS [Quantidade de Parcelas Amortização],
    AddMonths(GDPCONTR_DT_INIC_AMORTIZACAO, IF(PRDO_CD_AMORTIZACAO = 8, GDPCONTR_QT_PARCELA_AMORT * 6, GDPCONTR_QT_PARCELA_AMORT))
                                                                                        AS [Data Fim Amortização]
    FROM
[$(Caminho)\GDP_CONTRATO.QVD]
(qvd)
WHERE GDPCONTR_NU = '2842';

Em outra parte do Script eu gero as datas e valores da seguinte forma:

LET NumRows = NoOfRows('GDP_CONTRATO')-1;


FOR i=0 to $(NumRows)
  LET vChave = Peek('ChaveContrato', $(i), 'GDP_CONTRATO');
  LET vQtParcelas = Peek('Quantidade de Parcelas Amortização', $(i), 'GDP_CONTRATO');
  LET vDtIniAmortizacao = Peek('Data Inicio Amortização', $(i), 'GDP_CONTRATO');
  LET vDtFimAmortizacao = Peek('Data Fim Amortização', $(i), 'GDP_CONTRATO');
  LET vVlContrato=Peek('Valor Total do Contrato', $(i), 'GDP_CONTRATO');
  LET vSaldo = '$(vVlContrato)';

  FOR J=1 TO $(vQtParcelas)

  LET vAmortizacao = '$(vSaldo)'/('$(vQtParcelas)'-'$(J)'+1);
  LET vSaldo = '$(vSaldo)'-'$(vAmortizacao)';


  CONTRATO_PARCELAS:
  LOAD '$(vChave)' as ChaveContrato,
  '$(J)' as NU_Parcela,
  '$(vQtParcelas)' as QT_TotaisParcelas,
  '$(vDtIniAmortizacao)' as DT_IniAmortizacao,
  '$(vDtFimAmortizacao)' as DT_FimAmortizacao,
  Num('$(vVlContrato)','#.##0,00;-R$ #.##0,00') as VL_Contrato,
  Num('$(vAmortizacao)','#.##0,00;-R$ #.##0,00') as VL_Amortizacao,
  Num('$(vSaldo)','#.##0,00;-R$ #.##0,00') as VL_Saldo

  AutoGenerate 1;
  LET vSaldo = '$(vSaldo)';

  NEXT;
NEXT;

Resultando em uma tabela da seguinte forma:

Dessa forma está correta pois o valor do Saldo está diminuindo conforme estabelecido no Script nessa parte aqui:

LET vSaldo = '$(vSaldo)'-'$(vAmortizacao)';

Até aí eu não tive problemas, porém foi estabelecido um novo requisito informando que o valor do Saldo a ser utilizado como base para a projeção deveria ser o valor atualizado do contrato.

Exemplo:

O contrato acima foi assinado em DEZ/2012 mas só começou a amortizar em JAN/2015, tendo 216 parcelas (mensais) de pagamento, nesse caso ao invés de trabalhar com a Data de Início da Amortização (2015) passei a projetar a partir da Data de Assinatura do Contrato (2012), sempre calculando as 216 parcelas.

O Valor Atual do contrato pode sofrer variação, no caso de terem sido acrescidos novos valores com o passar do tempo.

Para esse contrato específico houve uma variação semelhante a exposta abaixo:

Mês	Ano	Liberações	Saldo Curto Prazo	Saldo Longo Prazo	Status
12	2012	430.903.122,39	0	430.903.122,39	2
1	2013	0	0	430.903.122,39	2
2	2013	0	0	430.903.122,39	2
3	2013	0	0	430.903.122,39	2
4	2013	0	0	430.903.122,39	2
5	2013	430.367.084,75	0	861.270.207,14	2
6	2013	0	0	861.270.207,14	2
7	2013	0	0	861.270.207,14	2
8	2013	0	0	861.270.207,14	2
9	2013	0	0	861.270.207,14	2
10	2013	0	0	861.270.207,14	2
11	2013	0	0	861.270.207,14	2
12	2013	0	0	861.270.207,14	2
1	2014	0	3.987.362,07	857.282.845,07	2
2	2014	0	7.974.724,14	853.295.483,00	2
3	2014	0	11.962.086,21	849.308.120,93	2
4	2014	0	15.949.448,28	845.320.758,86	2
5	2014	0	19.936.810,35	841.333.396,79	2
6	2014	0	23.924.172,42	837.346.034,72	2
7	2014	0	27.911.534,49	833.358.672,65	2
8	2014	0	31.898.896,56	829.371.310,58	2

E por aí vai, podendo haver novas liberações até a Data Final do Contrato (Data de Assinatura do Contrato * Número de Parcelas)

Uma vez exposto esses dados irei explicar o novo requisito solicitado:

Projeção da Dívida Baseado no Valor da Atual do Contrato.

O valor atual do contrato é a soma dos dois campos exibidos acima,

[Saldo de Curto Prazo No Último Dia do Mês] + [Saldo de Longo Prazo No Último Dia do Mês] = [Saldo Atual]

Esse campo é carregado a partir de outra tabela da seguinte forma:

GDP_IMPORT_PLANH_CONTRATO:

LOAD

GDPIMPLCON_SQ AS ChaveImportPlanhContrato,

GDPIMPLCON_NU_CONTRATO AS [Numero do Contrato],

GDPIMPLCON_DT_ANO AS [Ano de Referência do Exercício],

GDPIMPLCON_NU_MES AS [Mês Que Aconteceu a Movimentação Financeira],

Num(GDPIMPLCON_VL_AMORT,'#.##0,00;-R$ #.##0,00') AS [Valor Total Das Amortizações No Mês],

Num(GDPIMPLCON_VL_COMPL_JUROS,'#.##0,00;-R$ #.##0,00') AS [Valor Complementar dos Juros],

Num(GDPIMPLCON_VL_JUROS,'#.##0,00;-R$ #.##0,00') AS [Valor Total dos Juros do Mês],

Num(GDPIMPLCON_VL_OUTROS_ENCARGOS,'#.##0,00;-R$ #.##0,00') AS [Valor Total de Outros Encargos No Mês],

Num(GDPIMPLCON_VL_LIBERACAO,'#.##0,00;-R$ #.##0,00') AS [Valor Total da Liberação No Mês],

Num(GDPIMPLCON_VL_JUROS_CAPIT,'#.##0,00;-R$ #.##0,00') AS [Valor Total dos Juros Capitalizados No Mês],

Num(GDPIMPLCON_VL_CORR_MONETARIA,'#.##0,00;-R$ #.##0,00') AS [Valor Total da Correção Monetária Do Mês],

Num(GDPIMPLCON_VL_CORR_CAMBIAL,'#.##0,00;-R$ #.##0,00') AS [Valor Total da Correção Cambial Do Mês],

Num(GDPIMPLCON_VL_SALD_CURTO_PRAZO,'#.##0,00;-R$ #.##0,00') AS [Saldo de Curto Prazo No Último Dia do Mês],

Num(GDPIMPLCON_VL_SALD_LONGO_PRAZO,'#.##0,00;-R$ #.##0,00') AS [Saldo de Longo Prazo No Último Dia do Mês],

Num(GDPIMPLCON_VL_SALD_CURTO_PRAZO + GDPIMPLCON_VL_SALD_LONGO_PRAZO,'#.##0,00;-R$ #.##0,00') AS [Saldo Atual],

Num(GDPIMPLCON_VL_JUROS_PRO_RATA,'#.##0,00;-R$ #.##0,00') AS [Valor Dos Juros Pró-rata],

IF(GDPIMPLCON_IN_STATUS='0', 'Dados Provisórios', IF(GDPIMPLCON_IN_STATUS='1', 'Fechamento do Mês', 'Fechamento dos Valores a Serem Pagos'))

AS [Status dos Dados Do Contrato],

GDPIMPLCON_DT_IMPORTACAO AS [Data da Importação],

GDPIMPLCON_DT_UTIL_IMPORT AS [Data do Pagamento]

FROM

[$(Caminho)\GDP_IMPORT_PLANH_CONTRATO.QVD]

(qvd)

WHERE GDPIMPLCON_IN_STATUS = 2;

Inner Join (GDP_IMPORT_PLANH_CONTRATO)

LOAD

[Numero do Contrato],

MAX([Ano de Referência do Exercício] & Right('0'& [Mês Que Aconteceu a Movimentação Financeira],2)) AS AnoMes

Resident GDP_IMPORT_PLANH_CONTRATO

Group By [Numero do Contrato];

Faço um INNER JOIN dessa tabela com a tabela de CONTRATOS apresentada anteriormente, então o usuário irá filtrar o Ano e o Mês para selecionar o Valor Atual que deseja fazer a projeção e essa tabela será recalculada conforme essa ação.

Segue uma imagem contendo a apresentação prévia com esse cenário:

Agora relato o meu problema:

A princípio eu tentei mudar apenas os campos correspondentes aos valores, ficando o Script dessa forma:

LET NumRows = NoOfRows('GDP_CONTRATO')-1;


FOR i=0 to $(NumRows)
  LET vChave = Peek('ChaveContrato', $(i), 'GDP_CONTRATO');
  LET vQtParcelas = Peek('Quantidade de Parcelas Amortização', $(i), 'GDP_CONTRATO');
  LET vDtIniAmortizacao = Peek('Data Inicio Amortização', $(i), 'GDP_CONTRATO');
  LET vDtFimAmortizacao = Peek('Data Fim Amortização', $(i), 'GDP_CONTRATO');
  LET vVlContrato=Peek('Valor Total do Contrato', $(i), 'GDP_CONTRATO');
  LET vSaldoAtual = Peek('Saldo Atual', $(i), 'GDP_CONTRATO');
  
  
  FOR J=1 TO $(vQtParcelas)
  
  LET vSaldoAtual = '$(vSaldoAtual)';
  LET vAmortizacao = '$(vSaldoAtual)'/('$(vQtParcelas)'-'$(J)'+1);
  IF  '$(J)' > '1' THEN
  LET vSaldoAtual = '$(vSaldoAtual)'-'$(vAmortizacao)';
  LET vAmortizacao = '$(vSaldoAtual)'/('$(vQtParcelas)'-'$(J)'+1);
  ENDIF

  CONTRATO_PARCELAS:
  LOAD '$(vChave)' as ChaveContrato, 
  '$(J)' as NU_Parcela,
  '$(vQtParcelas)' as QT_TotaisParcelas,
  '$(vDtIniAmortizacao)' as DT_IniAmortizacao,
  '$(vDtFimAmortizacao)' as DT_FimAmortizacao,
  Num('$(vVlContrato)','#.##0,00;-R$ #.##0,00') as VL_Contrato,
  Num('$(vAmortizacao)','#.##0,00;-R$ #.##0,00') as VL_Amortizacao,
  Num('$(vSaldoAtual)','#.##0,00;-R$ #.##0,00') as VL_SaldoAtual


  AutoGenerate 1;
  LET vSaldoAtual = '$(vSaldoAtual)';


  NEXT;
NEXT;

Porém, por algum motivo que ainda desconheço,os dados não estão vindo corretamente preenchidos.

Tomando com exemplo o Valor do Saldo Atual desse contrato para o Ano de 2016 e Mês de Março o Saldo Atual é de R$ 984.166.875,15, consequentemente o Valor da Amortização deveria ser de R$ 4.556.328,13 (984.166.875,15 / 216)

E o saldo seria atualizado de forma decrescente sempre pegando o valor do Saldo da linha anterior e subtraindo pelo valor da Amortização

Porém quando carrego os mesmos campos na Apresentação, é exibido conforme a imagem abaixo:

São criados 18 registros para cada Número da Parcela (1 - 215) e um único registro com o valor 0 para a parcela 216

(Saliento que nessas imagens estou sempre filtrando por um único contrato com as mesmas 216 parcelas)

Ao analisar os valores gerados identifiquei que para o campo de Saldo Atual os dados existem conforme as imagens abaixo:

Como não consegui entender o motivo desse problema, passei a tentar fazer os cálculos através de Expressões já na Apresentação, contudo, para o cálculo do Saldo Atual que necessita pegar o valor da célula anterior e subtrair do valor da Amortização eu não obtive sucesso.

Tentei resolver da seguinte forma:

Porém, não consegui fazer com que o Valor do Saldo Vá sendo atualizado.

O Saldo Atualizado deveria ser o Valor do Saldo da Célula Anterior - Valor da Amortização

Vocês teriam novas sugestões de como posso resolver esse problema?

Sendo direto pelo Script ou mesmo por Expressão diretamente na Apresentação.

Obrigado!

Report Inappropriate Content · ‎2016-05-04

se usar ABOVE(Saldo) ele vai pegar o saldo da linha anterior

kkmoraes · ‎2016-05-04

Eduardo, Tudo bem?

Apliquei o = Above(Saldo) - [Valor da Amortização], porém só o valor do primeiro registro foi alterado, os demais se mantiveram, eu conseguiria capturar de cada célula?

O resultado foi esse:

Se eu Aplicar da seguinte forma:

= Above(SaldoAtual - [Valor da Amortização])

O valor vem zerado, já que para o primeiro registro do Saldo Atual não teria nada.

nicolett_yuri · ‎2016-05-04

Kleiton, tente com o RangeSum e Above.

Veja um exemplo de como se aplicar aqui: https://blogdonicolett.com.br/2015/05/31/acumulado-com-expressao/

kkmoraes · ‎2016-05-04

Ola Yuri, Obrigado pela resposta!

Desculpe a ignorância pois ainda não havia trabalhado com essas funções, apliquei dessa forma:

RangeSum(Above(Saldo), Saldo - [Valor da Amortização])

Porém a alteração foi feita apenas no primeiro registro, conforme apresentado na imagem abaixo:

Pesquisei alguns exemplos e vi algumas expressões utilizando o RowNo() como parâmetro para o RangeSum(), fiz algumas tentativas aqui porém ainda sem sucesso.

Você saberia me informar se a aplicação desta função que você recomendou está correta?

Obrigado!

Clever_Anjos · ‎2016-08-05

Já achou uma solução? Se sim, marque por gentileza uma das respostas como "Correta"

Como obter ajuda? Leia antes de postar

Ajuda com Tabela de Projeção de Dados

Desktop

Scripting