Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
kkmoraes
New Contributor III

Ajuda com Tabela de Projeção de Dados

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:

Saldo0.png

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êsAnoLiberaçõesSaldo Curto PrazoSaldo Longo PrazoStatus
122012430.903.122,390430.903.122,392
1201300430.903.122,392
2201300430.903.122,392
3201300430.903.122,392
4201300430.903.122,392
52013430.367.084,750861.270.207,142
6201300861.270.207,142
7201300861.270.207,142
8201300861.270.207,142
9201300861.270.207,142
10201300861.270.207,142
11201300861.270.207,142
12201300861.270.207,142
1201403.987.362,07857.282.845,072
2201407.974.724,14853.295.483,002
32014011.962.086,21849.308.120,932
42014015.949.448,28845.320.758,862
52014019.936.810,35841.333.396,792
62014023.924.172,42837.346.034,722
72014027.911.534,49833.358.672,652
82014031.898.896,56829.371.310,582

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:

Saldo2.png

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:

SaldoErrado1.png

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:

SaldoErrado2.png

SaldoErrado3.png

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:

Saldo3.png

Saldo4.png

Saldo5.png

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!

5 Replies
Not applicable

Re: Ajuda com Tabela de Projeção de Dados

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

kkmoraes
New Contributor III

Re: Ajuda com Tabela de Projeção de Dados

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:

SaldoErrado5.png

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.

Re: Ajuda com Tabela de Projeção de Dados

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
New Contributor III

Re: Ajuda com Tabela de Projeção de Dados

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:

GDP1.png

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!

Employee
Employee

Re: Ajuda com Tabela de Projeção de Dados

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

Como obter ajuda? Leia antes de postar