5 Replies Latest reply: Aug 5, 2016 8:30 AM by Clever Anjos RSS

    Ajuda com Tabela de Projeção de Dados

    Kleiton Moraes

      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!