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

Trying to calculate percentage between two meassures

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?

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

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

Hi Shinichiro, thanks!

Here it goes:

Loan:

Loan1:

 PreEmp   (PK) PreSuc (Pk) PreCbte (PK) PreNroI (PK) PreRCtaNum (PK) PreRCtaFeIni PreRCtaFeVen PreRCtaMonto 1 1 01-001-00001-PREB 1 1 04/06/2014 10/08/2014 \$ 223,00 1 1 01-001-00001-PREB 1 2 11/08/2014 10/09/2014 \$ 223,00 1 1 01-001-00001-PREB 1 3 11/09/2014 10/10/2014 \$ 223,00 1 1 01-001-00001-PREB 1 4 11/10/2014 10/11/2014 \$ 223,00 1 1 01-001-00001-PREB 1 5 11/11/2014 10/12/2014 \$ 223,00 1 1 01-001-00001-PREB 1 6 11/12/2014 10/01/2015 \$ 223,00 1 1 01-001-00001-PREB 1 7 11/01/2015 10/02/2015 \$ 223,00 1 1 01-001-00001-PREB 1 8 11/02/2015 10/03/2015 \$ 223,00 1 1 01-001-00001-PREB 1 9 11/03/2015 10/04/2015 \$ 223,00 1 1 01-001-00001-PREB 1 10 11/04/2015 10/05/2015 \$ 223,00 1 1 01-001-00001-PREB 1 11 11/05/2015 10/06/2015 \$ 223,00 1 1 01-001-00001-PREB 1 12 11/06/2015 10/07/2015 \$ 223,00 1 1 01-001-00001-PREB 1 13 11/07/2015 10/08/2015 \$ 223,00 1 1 01-001-00001-PREB 1 14 11/08/2015 10/09/2015 \$ 223,00 1 1 01-001-00001-PREB 1 15 11/09/2015 10/10/2015 \$ 223,00 1 1 01-001-00001-PREB 1 16 11/10/2015 10/11/2015 \$ 223,00 1 1 01-001-00001-PREB 1 17 11/11/2015 10/12/2015 \$ 223,00 1 1 01-001-00001-PREB 1 18 11/12/2015 10/01/2016 \$ 223,00 1 1 01-001-00001-PREB 2 1 05/06/2014 29/07/2014 \$ 508,00 1 1 01-001-00001-PREB 2 2 30/07/2014 29/08/2014 \$ 508,00 1 1 01-001-00001-PREB 2 3 30/08/2014 29/09/2014 \$ 508,00 1 1 01-001-00001-PREB 2 4 30/09/2014 29/10/2014 \$ 508,00 1 1 01-001-00001-PREB 2 5 30/10/2014 29/11/2014 \$ 508,00 1 1 01-001-00001-PREB 2 6 30/11/2014 29/12/2014 \$ 508,00 1 1 01-001-00001-PREB 2 7 30/12/2014 29/01/2015 \$ 508,00 1 1 01-001-00001-PREB 2 8 30/01/2015 28/02/2015 \$ 508,00 1 1 01-001-00001-PREB 2 9 01/03/2015 29/03/2015 \$ 508,00 1 1 01-001-00001-PREB 2 10 30/03/2015 29/04/2015 \$ 508,00 1 1 01-001-00001-PREB 2 11 30/04/2015 29/05/2015 \$ 508,00 1 1 01-001-00001-PREB 2 12 30/05/2015 29/06/2015 \$ 508,00 1 1 01-001-00001-PREB 2 13 30/06/2015 29/07/2015 \$ 508,00 1 1 01-001-00001-PREB 2 14 30/07/2015 29/08/2015 \$ 508,00 1 1 01-001-00001-PREB 2 15 30/08/2015 29/09/2015 \$ 508,00 1 1 01-001-00001-PREB 2 16 30/09/2015 29/10/2015 \$ 508,00 1 1 01-001-00001-PREB 2 17 30/10/2015 29/11/2015 \$ 508,00 1 1 01-001-00001-PREB 2 18 30/11/2015 29/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

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])

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

Create relationships with two data.