EMI/Receivalbe balance amount with respect to Invoice Amount

[Former Member]

Hi,

I have requirement like Invoice total amount and Receipt amount. Every month it has to accumulate the receipt amount and need to tell Open Receivable amount for that particicular month.

Eg: We have purchased a car and paying EMI every month and need to know the balance of the every month. Need to add the receipt amount of previous months paid and this month paid and tell the total balance against to Total Invoice amount.

Below is the some rough numbers,I want to plot Line chart for this Open Receivable amount with respect to the Total Invoice Amount with respect to Receipt month. Can you please let me know, how can we achieve this requirement ?

This is raw data Table:-

Invoice Date      Receipt Date      Receipt Amount      Open Receivable      Invoice Total Amount

November      12/31/2013           119                           2,328                     2,447 

November      1/22/2014             120                           2,327                     2,447 

November      1/31/2014             576                           1,871                     2,447 

November                                     0                           2,447                     2,447

                                       Sum: 815                           

I need in this way :-

Invoice Date      Receipt Date      Receipt Amount      Open Receivable      Invoice Total Amount

November      12/31/2013           119                           2,328                     2,447 

November      1/22/2014             120                           2,208                     2,447 

November      2/31/2014             576                           1,632                     2,447 

November                                     0                           1,632                     2,447

                                       Sum: 815                           

[sateesh_kumar1 · Accepted solution]

Hi Sai,

Guess you have Invoice Number in the table.

Open Receivable = Invoice Amount - Runningsum([Receipt Amount];([Invoice Number]))

[Former Member · Accepted solution]

Thank you Satish.. it's working perfect.

[Former Member · Accepted solution]

Hi Sathis,

I have same other requirement for SHOP. I have same Objects as above meaning Invoice Date, Invoice Total amount, Receipt date and Receipt Amount and Group ID for the Account.

I need to count the Group ID where it's not 100 % paid.

Can you please let me know, how we can achive this requirement as well ?

Thanks

[sateesh_kumar1 · Accepted solution]

Hi Sai,

How group Id is assigned.Is it 1:1 for invoice number?

How to find 100% paid.Is it Invoice Amount-Receipt Amount/Invoice Amount ?

If you have 1  group ID for 1 Invoice number  then

=Count([Group ID] Where ([%]<100) In ([Invoice Number))

[Former Member · Accepted solution]

Hi Sathish,

One Group(SHOP) ID would be having many Invoice numbers.. Liket SHOP (GroupID) would be having many Invoices.

I want to count the Group ID where it's not 100 % paid of the Invoice Total amount.

also ([%]<100) is not the correct expression, i guess.

Thanks

[sateesh_kumar1 · Accepted solution]

[%]  =(Invoice Amount-Receipt Amount)/Invoice Amount*100

Try with below formula

=Count([Invoice Number] Where ([%]<100) In ([Material group])) .

Here <100 is to say amount is not paid totally.