3 Replies Latest reply on Aug 16, 2016 6:39 AM by Shinichiro Murakami

    Trying to calculate percentage between two meassures

    Federico Barbero

      I have this three tables Joined "Loan", "Loan1" and "Loan3".

      Now "Loan" have the information about a loan given to a client. "Loan1" have the information about all the "quotas" (Ex. 1 loan have 18 lines in the table Loan1, for each "quota"), all the loans that are in table "Loan" are in table "Loan1".

      Now "Loan3" have all the Payments that the clients made for each "quota". That means, for each line in Loan1 there are 0 to N lines in table Loan3.

      I need to know the percentage of payments made in each month for all the "quotas" of, for example, January.

      Let me do a table:

      Amount of money of all the quotas with due date "January/16"U$D 100
      Amount paid in "January/16"50%
      Amount paid in "February/16"20%
      Amount paid in "March/16"10%
      Amount paid in "April/16"5%
      Amount paid in "May/16"2%

       

      the amount of money of all the quotas with due date in "January/16" it can be calculated from "Loan1", and the amount paid from "Loan3".

       

      How can I do that?

       

      Thanks in advance,

        • 1. Re: Trying to calculate percentage between two meassures
          Shinichiro Murakami

          Federico,

           

          Could you share the data format of each three table of Loan, Loan 1, and Loan 3?

          Excel file is good or even HTML works.

           

          Thanks,

          Shin

          • 2. Re: Trying to calculate percentage between two meassures
            Federico Barbero

            Hi Shinichiro, thanks!

            Here it goes:

            Loan:

            Loan1:

             

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

            PreEmp   (PK)PreSuc (Pk)PreCbte (PK)PreNroI (PK)PreRCtaNum (PK)PreRCtaFeIniPreRCtaFeVenPreRCtaMonto
            1101-001-00001-PREB1104/06/201410/08/2014$ 223,00
            1101-001-00001-PREB1211/08/201410/09/2014$ 223,00
            1101-001-00001-PREB1311/09/201410/10/2014$ 223,00
            1101-001-00001-PREB1411/10/201410/11/2014$ 223,00
            1101-001-00001-PREB1511/11/201410/12/2014$ 223,00
            1101-001-00001-PREB1611/12/201410/01/2015$ 223,00
            1101-001-00001-PREB1711/01/201510/02/2015$ 223,00
            1101-001-00001-PREB1811/02/201510/03/2015$ 223,00
            1101-001-00001-PREB1911/03/201510/04/2015$ 223,00
            1101-001-00001-PREB11011/04/201510/05/2015$ 223,00
            1101-001-00001-PREB11111/05/201510/06/2015$ 223,00
            1101-001-00001-PREB11211/06/201510/07/2015$ 223,00
            1101-001-00001-PREB11311/07/201510/08/2015$ 223,00
            1101-001-00001-PREB11411/08/201510/09/2015$ 223,00
            1101-001-00001-PREB11511/09/201510/10/2015$ 223,00
            1101-001-00001-PREB11611/10/201510/11/2015$ 223,00
            1101-001-00001-PREB11711/11/201510/12/2015$ 223,00
            1101-001-00001-PREB11811/12/201510/01/2016$ 223,00
            1101-001-00001-PREB2105/06/201429/07/2014$ 508,00
            1101-001-00001-PREB2230/07/201429/08/2014$ 508,00
            1101-001-00001-PREB2330/08/201429/09/2014$ 508,00
            1101-001-00001-PREB2430/09/201429/10/2014$ 508,00
            1101-001-00001-PREB2530/10/201429/11/2014$ 508,00
            1101-001-00001-PREB2630/11/201429/12/2014$ 508,00
            1101-001-00001-PREB2730/12/201429/01/2015$ 508,00
            1101-001-00001-PREB2830/01/201528/02/2015$ 508,00
            1101-001-00001-PREB2901/03/201529/03/2015$ 508,00
            1101-001-00001-PREB21030/03/201529/04/2015$ 508,00
            1101-001-00001-PREB21130/04/201529/05/2015$ 508,00
            1101-001-00001-PREB21230/05/201529/06/2015$ 508,00
            1101-001-00001-PREB21330/06/201529/07/2015$ 508,00
            1101-001-00001-PREB21430/07/201529/08/2015$ 508,00
            1101-001-00001-PREB21530/08/201529/09/2015$ 508,00
            1101-001-00001-PREB21630/09/201529/10/2015$ 508,00
            1101-001-00001-PREB21730/10/201529/11/2015$ 508,00
            1101-001-00001-PREB21830/11/201529/12/2015$ 508,00

             

            Loan3:

             

             

            As I say, all the loans registered in table "Loan" are in table "Loan1", but in table "Loan3" we have only the loans that have been received a pay.

             

            Thanks!

            • 3. Re: Trying to calculate percentage between two meassures
              Shinichiro Murakami

              Federico,

               

              Still difficult to approach your request with given info.

              Struggling to understand "all the quotas with due date in "January/16""

               

              For example, if PreNrol 1 pay $11,150 in August, the Sept due becomes 33,450 or kept as 22,300?

              But anyway with assuming there are no left over, I simply calc monthly level summary.

               

               

              Create calculated field to show monthly level granularity data instead of date for both Load 1 and Loan 3.

               

              [YYYYMM]  // Loan 1

              year([Pre R Cta Fe Ven])*100+month([Pre R Cta Fe Ven])

               

              [YYYYMM] // Load 3

              Year([Date])*100+month([Date])

               

              Create relationships with two data.

               

               

               

              [Header]

              "Amount paid in "+ datename('month',[Pre R Cta Fe Ven])+"/"+right(str(year([Pre R Cta Fe Ven])),2)

               

              Thanks,

              Shin

               

              9.0 attached